Help

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

782 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Katie_Byrne
4 - Data Explorer
4 - Data Explorer

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’)

Screenshot 2021-03-09 102311
Screenshot 2021-03-09 101958

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

Any ideas?
Thank you in advance,
Katie

5 Replies 5
Katie_Byrne
4 - Data Explorer
4 - Data Explorer

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

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:

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.

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

image

image

Any other settings to change?

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

Thank you for you help!!

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!