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.
Mar 08, 2021 03:57 PM
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
Mar 09, 2021 07:45 PM
Hmmm has anyone got any ideas? I’m really quite stuck on this one…
Mar 09, 2021 09:03 PM
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.
Mar 09, 2021 10:56 PM
Thanks Justin. It doesn’t appear to have changed anything though. Here are revised screenshots:
Any other settings to change?
Mar 09, 2021 10:58 PM
Oooh sorry - never mind - it hadn’t refreshed. All works perfectly now.
Thank you for you help!!
Mar 10, 2021 06:24 AM
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!