Locking to a time zone

[Richard_Willia1]

This seems to be a commonly asked question but I am yet to see an answer that works for me. I am trying to build a base which will manage activity in our building in London UK.

There is never a time that I don’t want any times entered to be treated as Europe/London - even if I am overseas the times should always be entered and displayed as London times, allowing for changes to GMT/BST when the clocks change.

One thought I had was to use a Date field for the Date, and a single line text field for the time. I could then use a formula to create the dateTime, however in doing this I don’t seem to be able to find a way to use SET_TIMEZONE to force the calculation into Europe/London at the point when I am creating the dateTime - it seems that this is always calculated into GMT.

A lot of Airtable support seems to suggest I use an offset to calculate this, but this seems to be deliberately ignoring the daylight savings issue. Putting in a single offset time just doesn’t work.

I feel I must be missing something as this seems like such a basic feature for Airtable to be lacking? Am I??

[Justin_Barrett]

The SET_TIMEZONE() function is finicky to say the least. It’s primarily designed to work with the DATETIME_FORMAT() function—i.e. to format a date into a string—but it does work in limited other cases with some of Airtable’s datetime-related function. I haven’t done exhaustive testing to know what those cases are, so it tends to be trial and error to know whether or not it will help when needing to apply an adjustment to a date.

That aside, there is a guaranteed way to specify the timezone: by including the offset directly as part of an ISO 8601-formatted time string. In short, the ISO 8601 standard formats dates and times like this example:

2022-05-28T13:18:00-07:00

The tail end of that is the timezone offset; in this example it’s the offset for PDT, my local timezone.

While the date from a date field could easily be combined with the time from a text field, you obviously don’t want to just tack on a static offset like you said. As time shifts when going into and out of daylight saving time, that would be annoying to maintain. Thankfully there’s a way around this as well.

I believe it was @kuovonne who shared a slick formula that automatically calculates the timezone offset between GMT and any other timezone. Here it is adjusted for London time:

"+0" & ABS(DATETIME_DIFF(
    DATETIME_PARSE(
        DATETIME_FORMAT(
            SET_TIMEZONE(NOW(), "Europe/London"), 
            "YYYY-MM-DD-HH-mm"
        ), 
        "YYYY-MM-DD-HH-mm"
    ), 
    NOW(), 
    "hours"
)) & ":00"

Put that in a formula field named {UTC Offset} and every record will have access to it.

Combining that with the other fields that you mentioned, this formula assembles the whole thing, then parses it to get the proper time:

IF(
  AND(Date, Time),
  DATETIME_PARSE(LEFT(Date, 10) & "T" & RIGHT("0" & Time, 5) & ":00" & {UTC Offset})
)

By default this new formula field will have the option activated to show the time based on GMT. Disable that setting to show the local time. (Note that you’ll have to enter times using 24-hour notation because that’s what the ISO 8601 standard requires, unless you develop yet another formula that converts 12-hour notation first.)

[kuovonne]

Dealing with timezones is tricky. Airtable always internally stores editable date/time objects as GMT. Even if you set the time in an editable date/time field using an offset, Airtable internally converts the date/time to GMT.

For one of my clients, the easiest solution was for her to leave her computer in her home timezone whenever she traveled. She used her phone to check the time versus computer time. Depending on how much you travel and what else you use your computer time for, this may or may not work for you.

A less than idea option is that you enter time as a local time (for whatever timezone your computer is using), but then have a formula field to always display the time time in Europe/London.

DATETIME_FORMAT(
  SET_TIMEZONE({DateTime}, "Europe/London"),
  "D/M/YYY h:mma"
) 

Parsing a date/time using a formula field and separate inputs for date and time is possible. It is even possible to calculate the timezonoe offset. However, it adds complexity to the base. You also need to make sure that you calculate the timezone offset for the given date/time, and not the current timezone offset. (The formula that @Justin_Barrett posted is based on NOW() and will give the current offset, not the offset at the time of the record, so it should not be used for historic records.)

[ScottWorld]

From the Airtable Universe, my sample base & training video on time zones might be of use to you:

Airtable

Time Zones: Working with multiple different time zones in Airtable - Airtable...

This Airtable base solves the problem of events taking place in different time zones, and users wanting to know what time those events are taking place in...

[Richard_William]

Thanks for these - all very interesting but all just demonstrate that, as far as I can see, there isn’t a decent solution for when our team are overseas - the comparison to NOW() only works for the current date but can’t be guaranteed for future dates (DST issues), and asking our team to lock their laptop to UK time purely to get around this airtable deficiency isn’t great.

I think I can make it work but it’s ugly - and really bizarre that it’s not something airtable seem interested in fixing.

[Elizabeth_Geri]

I know this is pretty old, but I've been tormenting myself with adjusting future dated appointments all day.  I was using IF statements to check the date and then add adjustments.  With the information here, I was able to do a relative calculation that automatically adjusts the time for daylight savings.  Game changer.  Thanks for sharing.