Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 07, 2023 04:17 AM
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’
Solved! Go to Solution.
Aug 07, 2023 06:42 AM - edited Aug 07, 2023 06:45 AM
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.
Aug 07, 2023 06:42 AM - edited Aug 07, 2023 06:45 AM
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.
Aug 08, 2023 02:56 AM
Thanks Sho - awesome suggestion, it fixed the problem straight away! Really appreciate you sharing your knowledge.
Ben