Skip to main content

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.

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🇲🇲ss')

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

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🇲🇲ss')

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


I really don't know what problem @JGrubka was trying to solve with that formula, or how they got kudos for it, but it has nothing to do with any time zone issues with TODAY(), etc. Airtable has not updated anything, and future readers would benefit from that response being deleted.

NOW() is already in GMT, so that formula is identical to this one:

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

All it does is needlessly convert the current GMT time to a string, and it will change every few minutes. You can verify this by creating those two formula fields yourself. Strings do not behave the same as real date types, and are mostly useless for date-based operations.

Do not blindly trust ChatGPT. I gave it many prompts and tried to help it correct its mistakes. It just apologised profusely and gave, at best, verbose formulae that had the same result and the same time zone issues as the native TODAY() function. At worst, it gave non-functioning code. The awful thing about ChatGPT is how misleading it is. Every response it gave was confidently asserted to be correct, but all were garbage. What it was able to do was explain how the correct formula from my first post works, when I pasted it in and asked it to. However, it cannot retain what it learns across different sessions, so it will keep giving out incorrect answers to others in future.

Reply


Terms & Conditions