Skip to main content
Solved

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

  • November 16, 2021
  • 6 replies
  • 88 views
H

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

Best answer by 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

6 replies

Hannah_Wiginton
Forum|alt.badge.img+19

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:

---Hannah - On2Air.com - Automated Backups for Airtable
H
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • Answer
  • November 16, 2021

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
H
A
Hannah_Wiginton
Forum|alt.badge.img+19

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!

---Hannah - On2Air.com - Automated Backups for Airtable
ScottWorld
H
  • Heidi_Wussow
  • Author
  • New Participant
  • November 16, 2021

Thanks to both of you! Both solutions worked.

Heidi

ScottWorld
Hannah_Wiginton
Justin_Barrett
Forum|alt.badge.img+21

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")
ScottWorld
Hannah_Wiginton
kuovonne
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 17, 2021

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.

Hannah_Wiginton
kuovonne
Justin_Barrett
Terms & ConditionsAccessibility statement