Dates and Times an Hour Off

[Luke_Annison]

Hi Everyone,

I’ve come across an issue where I can’t seem to get the date to print the correct time within a formula.

Please see example, I’ve stripped it down ot its bare bones.

On the left is a date (and the time is correct). On the right is that date reprinted as a function. It appears to be an hour off.

This is the formula for the function (simply reprinting the date):

It also gives the same result (incorrect) if I set the time to my time zone, which is GMT.

Any help appreciated

[kuovonne]

Luke_Annison:

It also gives the same result (incorrect) if I set the time to my time zone, which is GMT.

Have you checked the time zone setting for both the original field and the formula field? The settings for both fields must match.

Is your local time one hour off from GMT?

[Luke_Annison]

kuovonne:

Is

Hi Kuovonne,

Thanks very much for your respone. That was it. I’m actually in BST, not GMT currently (due to daylight saving Time).

I can’t seem to find my time zone however. If I use Europe/London it gives me the same result; reprinting the same date/time goes to an hour before.

Would you be able to advise as to how I find my time zone, which I guess would be GMT/ Europe/London with daylight saving time factored in.

Thanks very much for your help,

[kuovonne]

You should not need to “find” your time zone. Airtable uses the timezone of your local computer/browser.

If you are using a formula field with DATETIME_FORMAT() and SET_TIMEZONE(), the list of timezones is documented here. However, it seems like you are not using DATETIME_FORMAT(), and there is no need to use SET_TIMEZONE() on its own.

[Luke_Annison]

Ah that makes sense. It’s strange as my first attempt I don’t use either DATETIME_FORMAT() and SET_TIMEZONE() and it gives off a wrong hour, so it seems that the default doesn’t appear to be working as expected?

Thanks

[kuovonne]

If you go to this browser time zone test page. what time zone does it say you have?

In your first attempt, you probably had the default formatting options for each field, and they are different time zones. The default for an editable date/time field is local time. The default for a formula field is GMT.

[Luke_Annison]

It shows a discrepency actually. Maybe this is the cause of the problem?

[kuovonne · Accepted solution]

The screen capture shows that your browser knows your are in BST, which is an hour later than GMT. This matches what you were first seeing in and my suspicion that you had the default time zone settings for the fields.

By default, the editable date/time was in local tile (BST) and by default the formula field was in GMT, an hour earlier. If you set the format options of both fields to use local time, everything should look right.

[Luke_Annison]

Fantastic, that’s worked. Thanks very much for your help.