Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Time Zone Issue with TODAY() Formula

Topic Labels: Dates & Timezones
Solved
Jump to Solution
2016 2
cancel
Showing results for 
Search instead for 
Did you mean: 
bitgiggy
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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:

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

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:

Thank you! That’s very helpful.