Help

Having trouble with TODAY() or some other date functions? Results seem off? Don't use them!

Topic Labels: Dates & Timezones Formulas
982 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Lu
6 - Interface Innovator
6 - Interface Innovator

TODAY() and some similar functions are broken because they're stuck on Greenwich Mean Time. What it's meant to do is return a value that changes once per day, at the start of the day. It does this by setting the time part of the current date and time to 0 but, because Airtable's internal time zone is GMT/UTC and yours probably isn't, the result of the function will never be the start of your day. Any formulas that rely on it will have flawed results. The effects depend on how far from GMT your time zone is, and how accurate your formulas need to be. During summer in New Zealand, for example, with an offset of +13 hours, it behaves like a YESTERDAY() for more than half of each day. In California it becomes TOMORROW() early every evening. It can wreck reports and automations.

Changing the time zone in the formatting of any formula that uses it has no effect on the underlying value. Verify the problem yourself: create a formula field with only TODAY() as the content and set its formatting tab to show the time and use your local zone. The time part will show your (current) GMT offset instead of midnight and it will change at the wrong time of day.

A workaround is to create a formula field (in each table you need), and put in the following:

DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE(NOW(), "Country/City"), "YYYY-MM-DD[T00:00:00]ZZ"))

Change the "Country/City" part to a supported value and name the field something like TODAY_REAL, then use that in your other formulas. It's not pretty but is correct.

I brought this up with support in January 2023, suggesting that the built-in function should accept a time zone parameter. It still doesn't, and the docs do not mention this issue. The problem was originally caused by emulating spreadsheet programs like Excel that don't really care about time zones. A proper solution needs to come from Airtable.

The other affected functions include YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), & SECOND(), e.g.:

MONTH("2024-07-01T07:49:00+0800")

produces 6.

2 Replies 2
JGrubka
4 - Data Explorer
4 - Data Explorer

I believe they have updated this, as this function should do the trick! I'm using it on several of my bases 🙂 

DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'GMT'), 'YYYY-MM-DD HH:mm:ss')

Also, Chat GPT help me get to this, so I highly recommend using chat GPT to help solve any airtable issues!

Lu
6 - Interface Innovator
6 - Interface Innovator

They haven't updated it. Your formula is pointless as NOW() is already in GMT, except that the result type of yours is a string, not a real date, so it cannot be used for date-based operations.

Sorry, but I think you've missed the point of needing values that change based on a timezone other than GMT. ChatGPT has misled you.