Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Dates & Timezones
2342 5
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!