Date comparison doesnt work

[Ben_Phillips]

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’

[Sho · Accepted solution]

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.

[Ben_Phillips]

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

Ben