Returning a text value if a date is less than or equal to another date

[Heidi_Wussow]

Hello,
I’m trying to create a formula to return a value of “early deadline” if a field I have named {Application Date} contains a date less than or equal to 12/15/21. I’d also like to return a value of “Regular Deadline” if the date in the Application Date field is after 12/15/21.

This is a variation of the the formulas I’ve tried:

IF ({Application Deadline} <= (12/15/21),“Early Deadline”

I’m wondering if I need to add anything to qualify the 12/15/21 date. The date functions I’m finding look like they compare dates in two fields on the table, where my issue is that I only have one date on the table and am comparing it to another date that is not on the table.
When I try to save my formula I get an invalid formula error.

Thanks in advance!!

[Hannah_Wiginton]

Hey Heidi!

Welcome to the Airtable community!

You can create another date field and enter the 12/15/21 date in all rows. You can then hide that field. Then, the formula will work. :grinning_face_with_big_eyes:

Here’s the formula:

[ScottWorld · Accepted solution]

Another way of doing this would be to use a formula like this:

IF(IS_BEFORE({Application Deadline},"12/16/2021"),"Early Deadline")

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Hannah_Wiginton]

Good call, @ScottWorld! I haven’t used IS_BEFORE!

[Heidi_Wussow]

Thanks to both of you! Both solutions worked.

Heidi

[Justin_Barrett]

ScottWorld:

IF(IS_BEFORE({Application Deadline},“12/16/2021”),“Early Deadline”)

Frankly I’m surprised that this works because "12/16/2021" is a string, not a datetime. Most datetime-related functions like IS_BEFORE() require actual datetime values to operate correctly. However, after a few tests of my own, it appears that IS_BEFORE() is one of the rare exceptions.

Even though this does work, I’d probably be more inclined to use DATETIME_PARSE() to parse the string into a valid datetime for comparison:

IF(IS_BEFORE({Application Deadline},DATETIME_PARSE("12/16/2021", "MM/DD/YYYY")), "Early Deadline")

…or…

IF({Application Deadline} < DATETIME_PARSE("12/16/2021", "MM/DD/YYYY"), "Early Deadline")

[ScottWorld]

Yeah, I was surprised the string worked too! It actually recognizes many different strings as dates! For example, it even recognizes a string like “December 16, 2021”. But I agree that for best practices, using DATETIME_PARSE would be the safest way to go.