SOLUTION: Working with multiple different time zones in Airtable

[ScottWorld]

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

I’ve created a sample base & accompanying training video to solve the dilemma of working with multiple different time zones in 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 their own local time zone.

My sample base gives a properly-converted local time for any event — based on the event’s original date, time, and time zone.

This base could also be adapted to solve the problem of managing ANY resources that involve multiple time zones: employees, tracking packages, project deadlines, etc.

This solution is based on these 2 goals:

1. Each event uses its own local time zone for the “official” date/time of its event, and this date/time must always appear consistently, no matter who logs into the system. In other words, the GMT option must be turned ON in order to keep this date & time always appearing consistently to everyone. (The problem we’re solving in the sample base is that we can’t attach Time Zones to date/time fields, so whenever the GMT option is turned ON for a field, Airtable simply assumes that the date/time is GMT Time Zone. This would normally be fine, except that we really want to know what time this event takes place in our own own local Time Zone, and we can’t convert the time correctly if Airtable incorrectly assumes that the time we’re trying to convert is in GMT.)

2. The user logging into the system must be able to see BOTH the “official” date/time of the event (i.e. the date/time of the event in its own local time zone) AND be able to convert the date/time of the event into their own local time zone. This is actually Airtable’s default behavior — it will always automatically convert date/time fields into the local time zone whenever the GMT option is turned OFF for those fields. (The problem we’re solving in the sample base is that we can’t correctly convert the “official” date/time of the event if Airtable has assumed that the official date/time of the event was taking place in the GMT Time Zone.)

This sample base achieves both of the above goals, and you can also use this base as a starting-off point to solve additional time zone challenges that you might have.

Sample base here:

(Click “view larger version” on the embedded base below, or simply click on this link: Airtable - Time Zones by ScottWorld)

Airtable

Time Zones by ScottWorld - Airtable

Time Zones by ScottWorld Who is ScottWorld? We are expert Airtable trainers, consultants, and developers. If you need additional Airtable help, contact us at ScottWorld.com. This base solves the problem of events taking place in different time...

Accompanying video here:

As mentioned in both the video and within the field descriptions of the sample base:

a) Time Zone names MUST be pulled from the official list of accepted Time Zone names on Airtable’s website, which is located here:

Airtable Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

b) The 2 fields “Official Time of Event” and “Actual GMT Time of Event” must have their GMT option turned ON. The field “User’s Local Time of Event” must have its GMT option turned OFF.

Also, some other helpful pages:

List of supported format specifiers for DATETIME_FORMAT:

Airtable Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the format of DATETIME_FORMAT(Datetime, 'format sp...

Airtable’s formula reference:

Airtable Support

Formula field reference

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields. For an overview of formula fields, please refer to the Guide to Formula, Lookup, ...

Hope you guys find this solution helpful! :slightly_smiling_face:

[Jac_Evans]

Thanks for this!

If i need to say IS_BEFORE and use NOW() it is returning GMT but the UK is currently on BST. Is there way to manage this? I cant figure it out and I dont think its covered here? I tried setting timezone but outside of adding 1 hour to the time manually (or writing a complex IF forumla to do this when daylight saviings happens) I cant see how to do it!

[ScottWorld]

As I mentioned above, supported time zones are listed here: https://support.airtable.com/hc/en-us/articles/216141558-Supported-timezones-for-SET-TIMEZONE

Choose a time zone that supports BST.

[Jac_Evans]

I thought ‘Europe/London’ would be the right one but that doesnt work, will try some others, thanks!

[ScottWorld]

Are you trying to figure out if today’s date falls within BST?

There’s no need for you to worry about that when using my sample time zone base above, but if you want to do it anyways, you can easily do that with a simple formula.

I just looked up BST, and it looks like it takes place from the last Sunday of March to the last Sunday in October. This year, it’s from March 29 to October 25. I’m not sure what would be the ideal way to handle the fact that these aren’t the EXACT SAME DATES every year, but maybe the easiest thing would be to just update the formula once per year.

This formula would let you know if today’s date falls after March 28th and before October 26th. If so, it would result in a “Yes”. Otherwise, it results in “No”.

IF(

AND(
IS_AFTER(NOW(),DATETIME_PARSE("28 Mar 2020")),
IS_BEFORE(NOW(),DATETIME_PARSE("26 OCT 2020"))
),

"Yes","No")

That formula would yield the same result for all the records in your entire table, since that formula is just evaluating TODAY’S date.

But if you only wanted it to yield “Yes” for SOME of your records, you could just add other criteria into the AND part of the formula. Something like this:

IF(

AND(
{Official Time Zone of Event}="Europe/London",
IS_AFTER(NOW(),DATETIME_PARSE("28 Mar 2020")),
IS_BEFORE(NOW(),DATETIME_PARSE("26 OCT 2020"))
),

"Yes","No")

So that formula would only yield a “Yes” on the records marked as “Europe/London” AND if today’s date falls between March 28 & October 26.

[Jac_Evans]

Thank you! The issue with that is as you correctly pointed out, the dates keep changing. I can probably make do for now by manually adding in now+1hour when needed. I assumed this would be build in somewhere, I probably need to look a bit harder. Thank you for trying :slightly_smiling_face:

[ScottWorld]

Wait a second — I’m confused about why you think that BST isn’t accounted for? BST is already accounted for.

You can even verify this for yourself by installing the world clock block in your base, choosing the Europe/London time zone, and you’ll see that the time is accurate.

So, as you can see, Airtable is already accounting for BST, which would make sense, because they’re pulling in the official times for each one of their supported time zones.

[Jac_Evans]

ScottWorld:

Europe/London

That is NOW() and timezone now(), its an hour before the actual time in London.

You are correct though, the clock block displays the right time.

[ScottWorld]

Did you actually spend the time watching my video or playing with my demo base? I’m assuming NO, since I addressed all of these issues in both my video and in my sample base.

In fact, not only did I address these issues in my video and in my sample base, but it was the entire reason that I created the sample base & instructional video in the first place.

The whole reason that I spent the time to create this solution to begin with was to deal with multiple different time zones.

And yes, that includes BST time zone. BST time zone is no different than all the other time zones across the world that shift their clocks for 6 months of the year, such as DST (Daylight Savings Time).

So, once again, let me explain one of the many topics that I already covered in depth in my video:

YOU NEED TO TURN OFF THE GMT TIME FORMATTING ON YOUR FIELDS.

If you have (GMT) turned on for a date/time field in your system (or for a formula that results in date/time), Airtable will ALWAYS assume that that time is in GMT time. Always. Period. No exceptions to the rule. This is explained in my video & you can see it in action in my sample base.

So if you’re using the Now() function inside a formula field, but that formula field has the GMT time formatting turned on, then it will evaluate Now() in GMT time, not BST time — unless you turn off GMT.

Your Now() formulas are resulting in GMT time, because you have GMT turned on for your fields.

This is covered in depth in my video, and if you downloaded the sample base, you would also see that the sample base is already setup to work properly for you, right out of the gate.

Ironically, you don’t even need as complex of a solution as what I’ve created, because you’re not even dealing with multiple time zones — you’re just dealing with ONE single time zone. So in your case, you can just turn off the GMT formatting and be done with it.

But for other people who are juggling multiple different time zones, my solution is exactly the in-depth solution that they have been searching for.

So, even though your problem is solved with just a quick toggle of a button, I think you should still watch my video and download the sample base, because you’ll probably want to get a good grasp on how this works for all of your future time zone needs.

[Jac_Evans]

Ok, thanks for taking the time to reply. Cheers