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.
