Skip to main content

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

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:


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

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


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

Thanks to both of you! Both solutions worked.


Heidi

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



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

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.

Reply


Terms & Conditions