TODAY() reports wrong date

[Abhijeet_Rastog]

Sheet here: (can’t post due to new user)

Column 1 is “Date” type with the time field disabled.
Column 2 is “Formula” type: IF({Due Date} != "", {Due Date}, TODAY())

On row 2, if {Due Date} is empty, it automatically fills today’s date, ie, November 5, 2019.
On row 1, {Due Date} if set to April 20, 2019, for some reason reports Due Date? as April 19, 2019. GMT setting is disabled.

[Grant_Andrew]

Hello!

The reason for this is the IF statement needs to be wrapped in a SET_TIMEZONE and declare the desired timezone. You can find those here: https://support.airtable.com/hc/en-us/articles/216141558-Supported-timezones-for-SET-TIMEZONE

[W_Vann_Hall]

You can also set your formula field to use GMT. That may give you oddities for a few hours of the day (like 4 PM to midnight on the West Coast) for entries without a {Due Date}, but it will ensure {Due Date} and your formula field are in sync. Otherwise, you can do as @Grant_Andrew suggests and use SET_TIMEZONE().

Note, however, the documentation for SET_TIMEZONE() says it is only to be used in conjunction with DATETIME_FORMAT(). Obviously, that’s not necessarily the case, as Grant’s example demonstrates — but I don’t know if SET_TIMEZONE() affects the actual calculated datetime or just its display. (That is, while Grant’s solution of wrapping the formula in SET_TIMEZONE() may result in the correct date being shown, I don’t know if using the resulting value in another formula performs the calculation based on GMT or the time in the specified TZ; I suspect the former. If you need further to use that value, you’d want to test that. (I’d test it for you, except for me there is no rabbit hole like an Airtable datetime rabbit hole, and I’d really hate to confuse the heck out of myself right now…)

Also keep in mind when you configure a datetime field for “no timestamp,” Airtable assumes a timestamp of one clock tick after midnight for the date specified — even if you don’t display the timestamp. That’s why your displayed date appears to lead or trail your local date: A calculated date is always in GMT. So if you specify a date of April 20, 2019 with no timestamp, when Airtable needs to use that value in a calculation, it appends an assumed midnight timestamp and then assumes the value is given in GMT. (You don’t have a choice of whether date-only values are stored as GMT or local time; I think Airtable used to honor the GMT toggle for date-only values, but it presently doesn’t. I have a ticket open with Support to investigate this and flag it as a feature request if necessary.) When your formula assigns the value of {Due Date} to the formula field, it’s actually assigning it April 20, 2019 12:00 am GMT — which is April 19, 2019 4:00 pm PST, hence the mismatch.

At least I think that’s what’s happening…

[Dobs_Bobalina]

Can confirm, SET_TIMEZONE() only works at all in the context of the DATETIME_FORMAT() function, and even there it only changes the way the dates are displayed, not the underlying datetime. If using a dynamic date function like TODAY() in a formula that returns something other than a date, there seems to be no way to use the date and time at your current timezone.

So for example, if trying to use a simple DATETIME_DIFF() to count down the days until a deadline in your local timezone, the date and time will always be off unless you happen to live in GMT.

Can also confirm that unclicking the GMT option in the formatting section of a formula column will also only affect the way that date is displayed in that column, not how it is used in any further formulation.

All of this seems very counterintuitive to me, so I hope I’m wrong about something here.

[Dobs_Bobalina]

Solved my own issue— seems like any time you want to use a date function like TODAY() in a formula, you have to wrap it in DATETIME_FORMAT(SET_TIMEZONE({Your dynamic date or date function}, ‘Your_timezone_string’), ‘format-that-works-for-you’), then use DATETIME_FORMAT() again to match any dates you may need to compare.

There’s probably a reason why this makes sense to be coded this way, but to me still feels like far from a streamlined way to use dates

[SIMON_CLARK]

I came across this issue today as well, in the end creating a NOW() field adjusted to my timezone, and comparing to (in my case) a last modified field also adjusted to my timezone, and used the IS_SAME function restricted to 'day' appeared to work best:

IF(IS_SAME(NOW(), {Last Modified (ACTIVE/CANCELLED)}, 'day'), "Yes", "No")