Time Zone Issue with TODAY() Formula

[bitgiggy]

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

[Justin_Barrett · Accepted solution]

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:

Airtable Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

[bitgiggy]

Thank you! That’s very helpful.