Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Time Zone Issue with TODAY() Formula

Topic Labels: Dates & Timezones
Solved
Jump to Solution
505 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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.