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:
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.)
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: https://airtable.com/shrlXvr6L4K05asjX)
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:
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’s formula reference:
Hope you guys find this solution helpful!