Need formula to evaluate a date field with multiple entries

Options

Hello all,

I need help writing a formula that looks at a date field (which may have multiple entries) to see if it contains any dates within a set range. For example: if the "Date" field contains any dates earlier than May 5, 2022, return "1", otherwise, return "0".

Here's where I'm at...

=IF(CONTAINS(Date@row < DATE(2022, 05, 05))), 1, 0

Thanks in advance!

Mary

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Mary Ayers

    I can understand how the multi-select field would definitely be easier for your users filling out the form, and a quick way to view all the dates selected, however both this field and your helper column next to it can only read the numbers you've put in as Text.

    Dates in a Date Column need to be individual selections in order to be seen as a Date-type of data. This means that even if your "Test date field 2" is a date column, bringing in the data from the multi-select column with a formula is simply displaying text in the date cell instead of multiple dates.

    You could potentially filter based on if the Multi-Select HAS a specific value, for example " 06/01/22", but this would look only for that one value. There isn't a way to say "anything earlier than 06/01/22", unless you selected all possible date options that could be written in the multi-select field, does that make sense?

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Mary Ayers

    I hope you're well and safe!

    Is the Date column a Muli Select Column?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Mary Ayers

    When you say there are multiple entries, do you mean multiple rows, or multiple selections within one cell?

    If you're using a Date type of column, you can simply look for the date in your IF statement without CONTAINS, try:

    =IF(Date@row < DATE(2022, 05, 05), 1, 0)

    This would be a formula per-row, then you could count the entire checkboxes to have a full count. However, if that's your end-goal, you could use a COUNTIF Function to evaluate the entire column instead!

    =COUNTIF(Date:Date, < DATE(2022, 05, 05))

    If neither of these formulas have worked for you, it would be helpful to see screen captures of your sheet but please block out sensitive data.

    Cheers,

    Genevieve

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    edited 02/15/22
    Options

    Hello @Andrée Starå and @Genevieve P. ,

    Thanks to you both for trying to help me out!!

    There are multiple dates in one cell. "Date Test" is a Dropdown Multi Select field. The field has a default value in the form that describes the format in which each date should be entered:

    In order for the Multi Select field to show up on the form, there had to be data in the field, hence the descriptor of how to format the entry.

    "Test Date Field 2" is a Date field that brings in the dates from "Date Test".

    In this example, I want to create two filters:

    1) Test Date Field 2 contains dates less than 06/01/22

    2) Test Date Field 2 contains dates greater than or equal to 06/01/22

    Both of the example rows should show up in both filters, since both rows meet the criteria of both filters.

    Since I can't get the filter to work, I am hoping that adding two new fields (can be checkbox) to be filtered on would work. The first field is checked if "Test Date Field 2" contains dates earlier than 06/01/22; the second field is checked if "Test Date Field 2" contains dates equal to or later than 06/01/22.

    Please let me know if you need more info.

    Thanks!

    Mary

    P.S. I tried the formulas @Genevieve P. provided and neither worked.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Mary Ayers

    Thank you for the screen captures and this additional information, this definitely helps.

    The issue here is that the column type is a Multi-Select column. This type of column can only output text; the values that are selected won't be able to be evaluated as "dates", which means you can't look for something "greater than" or "less than" (either in a formula or in a filter).

    Is there a reason that you're using the Multi-Select field? What about having multiple Date type of columns in your sheet and form so you can collect the right type of data.

    You can hide these in your sheet and JOIN the values into one column if you prefer to see them all at once, but then your formula can use DATE to check the different dates in these hidden columns, or you can use a filter.

    Cheers,

    Genevieve

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    edited 02/16/22
    Options

    Hi @Genevieve P. ,

    Thanks for checking back in.

    The reason for the Multi-select field is so we can collect in the form multiple dates in one field. This is something that came out of a ProDesk appointment. The "Test Date Field 2" is a Date field which, per the formula in the second screenshot, pulls the dates from the "Date Test" field.

    I need to be able to filter on Test Date Field 2 (since it has the dates -- sometimes multiple in one cell -- in date format). I'm really trying to create a formula against "Test Date Field 2" but since multiple dates are in one field, I thought I would need to use the "CONTAINS" function.

    We want our users to be able to list all occurrence dates for the academic year in one form. We previously scheduled all occurrences in one batch. Our form had a "First Occurrence Date" field, then "Additional Dates" were listed in a text field. Starting last year, we are now scheduling in two batches (due to Covid). Batch 1: autumn/winter; Batch 2: spring/summer. Now that we're scheduling in two batches, we want to be sure to that all requests with spring/summer -- that also had autumn/winter dates -- are not overlooked when we schedule the spring/summer batch.

    It's much safer to be able to filter on all dates.

    Thanks again!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Mary Ayers

    I can understand how the multi-select field would definitely be easier for your users filling out the form, and a quick way to view all the dates selected, however both this field and your helper column next to it can only read the numbers you've put in as Text.

    Dates in a Date Column need to be individual selections in order to be seen as a Date-type of data. This means that even if your "Test date field 2" is a date column, bringing in the data from the multi-select column with a formula is simply displaying text in the date cell instead of multiple dates.

    You could potentially filter based on if the Multi-Select HAS a specific value, for example " 06/01/22", but this would look only for that one value. There isn't a way to say "anything earlier than 06/01/22", unless you selected all possible date options that could be written in the multi-select field, does that make sense?

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    edited 02/18/22
    Options

    Thank you, @Genevieve P. That is helpful to know. I will try a different approach: Two different fields for users to enter their dates: "Autumn/Winter Dates" & "Spring/Summer Dates". Then I can just filter on whether or not the fields are empty.

    Thanks again for the time and effort you put in to helping me try to resolve this issue!

    Enjoy the holiday weekend!

    Mary

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!