Formula for Date - Arghh inconsistently incorrect - timezone issue?!

[Katie_Byrne]

Hi there,

I have a “Ref” formula that is pulling the date from another field. Most of the time, when it’s an “AM” time, the date comes through incorrect. I’m guessing it’s perhaps a time zone thing?
I say “most” as there are a couple of mornings that ARE correct :woman_shrugging:

“Ref” formula =
{Name of Worker}&" - "&“Inc:”&DATETIME_FORMAT({Incident Date & Time}, ‘DD-MM-YYYY’)

In case it makes a difference, this data is pulling through via Zapier.

Any ideas?
Thank you in advance,
Katie

[Katie_Byrne]

Hmmm has anyone got any ideas? I’m really quite stuck on this one…

[Justin_Barrett]

This definitely looks like a timezone issue. It’s important to note that dates are actually stored internally based on GMT, which is why you have to force-format them to your local timezone everywhere else. Otherwise certain times might end up being displayed as a day difference. Try this tweaked version of your formula:

{Name of Worker} & " - Inc: " & DATETIME_FORMAT(SET_TIMEZONE({Incident Date & Time}, "TIMEZONE_HERE"), 'DD-MM-YYYY')

You’ll need to replace “TIMEZONE_HERE” with your local timezone based on the details here:

Airtable Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

I also merged the hyphen with the “Inc” portion. There’s no need to concatenate two adjacent literal strings when a single string will suffice.

[Katie_Byrne]

Thanks Justin. It doesn’t appear to have changed anything though. Here are revised screenshots:

Any other settings to change?

[Katie_Byrne]

Oooh sorry - never mind - it hadn’t refreshed. All works perfectly now.

Thank you for you help!!

[Justin_Barrett]

Glad that it’s working, although you technically have a typo in the way that you applied the timezone setting. You have two sets of quotes for the timezone string: double quotes around single quotes:

"'Australia/Adelaide'"

Only one quote type should be used, either single or double; e.g. 'Australia/Adelaide' or "Australia/Adelaide"

I tend to use double quotes just because I started programming ages ago when double quotes were the only option, so it’s more a habit than anything. :slightly_smiling_face: Anyway, if you want to use single quotes, the full formula should look like this:

{Name of Worker} & " - Inc: " & DATETIME_FORMAT(SET_TIMEZONE({Incident Date & Time}, 'Australia/Adelaide'), 'DD-MM-YYYY')

Frankly I’m surprised that the double-single combo actually works!