TODAY() reports wrong date

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.

1 Like

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

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…