Skip to main content

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

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.

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:








Any other settings to change?

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








Any other settings to change?


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



Thank you for you help!!

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. 🙂 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!

Reply


Terms & Conditions