Aug 20, 2021 10:11 AM
Hey, guys!
I’m trying to add a column that return to me a specific message when attends some date criteria.
Actually, I did the formula and it was working well:
IF(AND(DAY({Final date})=DAY({Request date}),MONTH({Final date})=MONTH({Request date}),YEAR({Final date})=YEAR({Request date})),“ :warning: Warning”, IF({Final date}<{Request date},“ :red_circle: Wrong”,“ :white_check_mark: Right”))
But after some tests I noticed that something weird was happening
How you can see, the first and the second line returned strange results…
Is there something wrong with the formula?
Thanks!
Solved! Go to Solution.
Aug 20, 2021 04:48 PM
Is “Use same timezone for everyone” turned on (or off) for both date fields? It is possible you’re dealing with timezone issues or either of your date fields are storing a time component behind the scenes.
Try:
IF(
DATETIME_FORMAT({Final Date}, "YYYYMMDD") = DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
"Warning",
IF(
DATETIME_FORMAT({Final Date}, "YYYYMMDD") < DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
"Wrong",
"Right"
)
)
If that doesn’t fix it, change every reference to the field names to SET_TIMEZONE({Name of field}, "your timezone")
. List of timezones: Supported timezones for SET_TIMEZONE – Airtable Support
Aug 20, 2021 04:48 PM
Is “Use same timezone for everyone” turned on (or off) for both date fields? It is possible you’re dealing with timezone issues or either of your date fields are storing a time component behind the scenes.
Try:
IF(
DATETIME_FORMAT({Final Date}, "YYYYMMDD") = DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
"Warning",
IF(
DATETIME_FORMAT({Final Date}, "YYYYMMDD") < DATETIME_FORMAT({Request Date}, "YYYYMMDD"),
"Wrong",
"Right"
)
)
If that doesn’t fix it, change every reference to the field names to SET_TIMEZONE({Name of field}, "your timezone")
. List of timezones: Supported timezones for SET_TIMEZONE – Airtable Support
Aug 20, 2021 05:23 PM
Thanks!
I was trying to solve this earlier and found the timezone option… Everything is right now.
By the way, I tried your formula and it’s worked as well. Actually, it’s much better and cleaner than mine’s! Blessed DATETIME_FORMAT.
Thank you, very much!