Skip to main content

SOLUTION: Working with multiple different time zones in Airtable

  • May 2, 2020
  • 9 replies
  • 133 views
ScottWorld
Forum|alt.badge.img+35

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)

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

    A
    Peter_Nelson2
    N

    9 replies

    J
    • Jac_Evans
    • Known Participant
    • May 2, 2020

    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
    Forum|alt.badge.img+35
    • ScottWorld
    • Author
    • Genius
    • May 2, 2020

    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!


    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.

    J
    • Jac_Evans
    • Known Participant
    • May 2, 2020

    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.


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

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Author
    • Genius
    • May 2, 2020

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


    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.

    J
    • Jac_Evans
    • Known Participant
    • May 2, 2020

    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.


    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
    Forum|alt.badge.img+35
    • ScottWorld
    • Author
    • Genius
    • May 2, 2020

    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:


    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.

    J
    • Jac_Evans
    • Known Participant
    • May 3, 2020

    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.


    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
    Forum|alt.badge.img+35
    • ScottWorld
    • Author
    • Genius
    • May 3, 2020

    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.


    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.

    J
    • Jac_Evans
    • Known Participant
    • May 3, 2020

    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.


    Ok, thanks for taking the time to reply. Cheers

    Terms & ConditionsAccessibility statement