Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Date comparison doesnt work

Topic Labels: Formulas
Solved
Jump to Solution
1223 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Ben_Phillips
4 - Data Explorer
4 - Data Explorer

Hi everyone,

This is my first post to the airtable community forum so please excuse any rookie mistakes 🙂

I'm having an issue with comparing two dates, I'm using the following formula...

IF(IS_AFTER({Today's date},{Estimated Programme End Date}), "Yes", "No")

Strangely, it seems for some records but not others.  Any ideas?

Thanks, in advance, for your help.

Kind regards

Ben

DATE COLUMN 1 FORMULA (Estimated Programme End Date)

DATETIME_FORMAT(IF({Programme}="NQPC",DATEADD({Created Date},70,'days'),IF({Programme}="BTH/MIT",DATEADD({Created Date},42,'days'),{IM - programme end data (from Initiation Weekend)})),'DD/MM/YYYY')

 

DATE COLUMN 2 FORMULA (Today’s date)

DATETIME_FORMAT(TODAY(), 'DD/MM/YYYY')

The comparison formula only works on occasion (row 1 doesnt work, row 2 and 3 do work, etc).  See screenshot below for sample output which shows the problem. 

 

Take row 1 as an example of the issue. 

‘Estimated Programme End Date’ = 20/06/2023

‘Today’s date’ = 07/08/2023

‘Programme finished’ = No despite ‘Today’s date’ being after ‘Estimated Programme End Date’

Ben_Phillips_0-1691406736504.png

 

 

 

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hi @Ben_Phillips ,

You might use the formatting of the field instead of using "Datetime_format" in all formulas.
Formula fields can be formatted for dates by Formatting if the output of the formula is a date.
"Datetime_format" outputs the date as a string and is no longer a date type. And Airtable tries to interpret the string date as a US format, so this is probably the result.

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

Hi @Ben_Phillips ,

You might use the formatting of the field instead of using "Datetime_format" in all formulas.
Formula fields can be formatted for dates by Formatting if the output of the formula is a date.
"Datetime_format" outputs the date as a string and is no longer a date type. And Airtable tries to interpret the string date as a US format, so this is probably the result.

Thanks Sho - awesome suggestion, it fixed the problem straight away!  Really appreciate you sharing your knowledge.

Ben