Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 31, 2021 03:14 PM
Hi everyone
I’m relying on using the TODAY() formula to mark fields true or false if a date = today. The trouble is, the formula is not working as I expect. Although I can clearly see the date is today, the formula shows FALSE, and if I change the date to yesterday, it becomes TRUE.
I am based in Asia, so I expect there is a conflict of timezones somewhere?
Does anyone know how I can correct this?
Thanks
Solved! Go to Solution.
Mar 31, 2021 05:51 PM
Both TODAY()
and NOW()
are calculated based on GMT. Specifically, TODAY()
uses the current day in GMT, but with the time set to midnight. When GMT changes to a new day, so will TODAY()
. NOW()
uses the actual current GMT time.
To compare a date field (manually-entered) against TODAY()
, you’ll need to format both dates and compare the formats. However, this means forcing TODAY()
to format based on your local timezone. Here’s an example using my local (US Pacific) timezone:
DATETIME_FORMAT(Date, "L") = DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "America/Los_Angeles"), "L")
That will output a 1 when the {Date}
field matches today, and 0 otherwise. Change “America/Los_Angeles” to your local timezone using the relevant value from this page:
Mar 31, 2021 05:51 PM
Both TODAY()
and NOW()
are calculated based on GMT. Specifically, TODAY()
uses the current day in GMT, but with the time set to midnight. When GMT changes to a new day, so will TODAY()
. NOW()
uses the actual current GMT time.
To compare a date field (manually-entered) against TODAY()
, you’ll need to format both dates and compare the formats. However, this means forcing TODAY()
to format based on your local timezone. Here’s an example using my local (US Pacific) timezone:
DATETIME_FORMAT(Date, "L") = DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "America/Los_Angeles"), "L")
That will output a 1 when the {Date}
field matches today, and 0 otherwise. Change “America/Los_Angeles” to your local timezone using the relevant value from this page:
Mar 31, 2021 09:31 PM
Thank you! That’s very helpful.