Skip to main content

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

K

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

    5 replies

    K
    • Katie_Byrne
    • Author
    • New Participant
    • 4 replies
    • March 10, 2021

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

    Justin_Barrett
    Forum|alt.badge.img+20

    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.

    K
    • Katie_Byrne
    • Author
    • New Participant
    • 4 replies
    • March 10, 2021
    Justin_Barrett wrote:

    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?

    K
    • Katie_Byrne
    • Author
    • New Participant
    • 4 replies
    • March 10, 2021
    Katie_Byrne wrote:

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

    Justin_Barrett
    Forum|alt.badge.img+20
    Katie_Byrne wrote:

    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!

    Reply


    Most helpful members this week

    Terms & Conditions