Need formula to evaluate a date field with multiple entries

Mary Ayers

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

Genevieve P.

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?

Andrée Starå

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!

Genevieve P.

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

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.

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

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.

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

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

Genevieve P.

https://community.smartsheet.com/discussion/comment/320169#Comment_320169

Ah, yes! This sounds like a good idea. 🙂