Help

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

Solved
Jump to Solution
1386 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Heidi_Wussow
5 - Automation Enthusiast
5 - Automation Enthusiast

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!!

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

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

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

See Solution in Thread

6 Replies 6

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:

image

Here’s the formula:

image

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable
ScottWorld
18 - Pluto
18 - Pluto

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

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

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

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable
Heidi_Wussow
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks to both of you! Both solutions worked.

Heidi

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")

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.