Oct 16, 2020 09:32 AM
I need help in Formula IF to detect someone birthday dues in today :
TODAY Formula : NOW() with European Format Column 1
Formula : DATETIME_PARSE(DATETIME_FORMAT(TLahir,‘MM/DD’)&"/"&YEAR(NOW())) column 2
Im Copy paste from : airtable Help (Recurring Date )
But when i put the Formula to detect Staff’s Birthday on Today : Column3 :
IF(Formula=Today,‘TODAY’,‘WRONG’)
The RESULT is always WRONG . why is that ?
Oct 16, 2020 10:24 PM
Welcome to the community, @Leonardo_Hadiprodjo! :grinning_face_with_big_eyes: Airtable’s date handling features are, unfortunately, a little messy IMO. Here’s what I suggest to (I hope) make things work more reliably.
One thing to be aware of is that Airtable’s NOW()
function includes the current time, whereas the date that you’re parsing only includes the month, day, and year. Even though you’re not displaying the time in the field, my understanding is that it’s still part of the behind-the-scenes calculation, which may explain why the comparison formula thinks they’re different.
In a situation like this, it’s actually going to be more reliable to parse the formatted date instead of the actual datetime object. In your {Today}
field, format NOW()
as you choose, making sure to include the SET_TIMEZONE()
function using your local timezone. This ensures that the calculation happens locally for you, not based on the default GMT.
In the {Formula}
field, drop the outer DATETIME_PARSE()
function and just keep the other formatting as you have it now, but also add the timezone control as you did with the other field. That will give you two text values to compare instead of two somewhat-finicky date values, which should give you more reliable results.
Oct 17, 2020 02:18 AM
I’ve tried but still FAILED .
DATETIME_FORMAT(SET_TIMEZONE(NOW(),‘Asia/Jakarta’),‘DD/MM/YYYY’)
DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE({Birth date},‘Asia/Jakarta’),‘MM/DD’)&"/"&YEAR(NOW()))
Oct 17, 2020 07:04 AM
You missed part of what I said above. Remove DATETIME_PARSE()
from around the second formula. If you parse the date you’re comparing a date against a string, which won’t work.