
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2020 01:27 AM
Hey there,
Wondering if you all can help me with a Timezone issue I’m having that’s driving me nuts! I think it’s to do with GMT/BST but I would like any help in solving this because I can’t get it right.
Trying to pair a Mentee and Mentor with each other, and they will live in different timezones, I can get the calendar invites to work, as it’s all based off nice and simple ISO time, but I can’t for the life of me get the timezone conversion to work in Airtable for local time (which I need for numerous other reasons related to communication)
I’m using this formula to convert the time as follows:
IF({Event Date + Start time}, DATETIME_FORMAT(SET_TIMEZONE(DATETIME_PARSE({Event Date + Start time}), {Mentor Timezone}), 'MM/DD/YYYY h:mm'),"")
However, I’m getting a delta across Mentee and Mentor, as shown below in both tables:
Can one of you kind folk help me work out what’s going on?
Mentor View
Mentee View
As you can see, there are 6 columns in each, and the problem is that the Actual time differs from the Calculated time.
- Event Date + Time - The ISO time that is converted to a calendar field
- Actual - What the conversion should be
- Calculated - What the conversion is when using the formula
Is this a bug or am I missing something?
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 01, 2020 01:39 PM
Time zones can be tricky. It took me a while to figure them out.
Here are some things to keep in mind:
-
All date/time fields are stored as GMT time internally.
-
In the user interface, date/times can be displayed as GMT time or the local time for the computer. This is controlled in the field formatting. This works for date/time fields and for formula fields that only return a date/time object (versus a string).
-
You can set the time zone in a formula field using a combination of
DATETIME_FORMAT
, andSET_TIMEZONE
. Note thatDATETIME_FORMAT
returns a string, not a date/time object. -
You do not need to use
DATETIME_PARSE
because you already have a date/time object. SaveDATETIME_PARSE
for when you want to turn a string into a date/time object. (For example, when converting a text field, or when creating a new date based on a month, day, and year.) -
There is no Airtable formula function to determine the timezone of the client computer.
Here are my thoughts:
-
You are on the right track by having a field for the time zone for each person. I recommend using a Single Select field for the time zone instead of a text field. That way you don’t have to worry about typos in the time zone.
-
You have six date/time input fields: three pairs of start/end times. Can you clarify the workflow that requires three pairs of times as inputs? I would expect that each session would have only one date/time input pair, and that the rest of the fields would be calculated.
-
Consider having a start date/time and a duration as your input fields. Then calculate the end date/time. That makes for is one less date/time input field, and one less field to deal with time zones.
-
Who will be doing the data entry? Will mentors & mentees on their on their own computers? A single a third party in a specific time zone? Multiple third parties in multiple time zones? This determines whether or not the input fields should be set to display local time or GMT time.
-
Some people like to set date/time fields to GMT time for everyone, but then they pretend that the GMT time is their local time. I highly recommend against this practice.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2020 01:52 AM
Hi @andywingrave,
Why dont you simply use the SET_TIMEZONE({Event Date + Start time}, {Mentee Time Zone})
, why do you need to use all of this formula ? Are you adding the date as a text or as a date field? The IF part I understand (so it doesn’t return an error if the field is empty), but why PARSE and FORMAT?
BR,
Mo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2020 01:57 AM
Hey @Mohamed_Swellam - Thanks for getting back to me.
That’s actually where I started off, but that was so wildly out of sync with what I needed. The If statement only states (If there is a time - i.e. has a meeting been booked), and the Datetime parse was the last hope of a desperate man.
Regardless - I’ve added it in to the last column, above, and you can see that there’s a 2 hour difference between the start time, and your formula. I’d definitely appreciate if someone could point me in the right direction here.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2020 02:01 AM
Oh so it didn’t work as I said? :grinning_face_with_big_eyes:
Probably the problem is Use the Same Timezone (GMT) for all Collaborators
. Make sure you use this in all the fields (even in the formula field). Remove the Format and Parse, it worked when I tried it.
BR,
Mo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2020 02:02 AM
Yep! I’m using that too! Can I see your solution, because I’ve added your solution to the table above, and you can see that there’s a 2 hour difference.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2020 02:18 AM
That is weird, it is working for me!
this is my base https://airtable.com/shrulkmiRc8REDSfi/tbloTQNhELV3F00R1?blocks=hide

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2020 02:20 AM
That hasn’t worked on your base, sadly - It should be the same as “Mentee Actual Start” - You’re just pulling in the “Event + Start Time”
:pray: :pray: :pray:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 29, 2020 09:07 AM
I couldn’t find a solution to this. I emailed support - Let’s hope they can figure it out.
In the meantime, I decided to manage timezones outside of Airtable, as it was causing too much confusion.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 01, 2020 11:35 AM
Hello,
Please see formula below as to how this can be accomplished for the specified use case!
DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE({Event Date + Start Time}, {Mentor Timezone}),'M/D/Y h:mm A'),'M/D/YYYY h:mm A')
Hope this helps!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 01, 2020 01:39 PM
Time zones can be tricky. It took me a while to figure them out.
Here are some things to keep in mind:
-
All date/time fields are stored as GMT time internally.
-
In the user interface, date/times can be displayed as GMT time or the local time for the computer. This is controlled in the field formatting. This works for date/time fields and for formula fields that only return a date/time object (versus a string).
-
You can set the time zone in a formula field using a combination of
DATETIME_FORMAT
, andSET_TIMEZONE
. Note thatDATETIME_FORMAT
returns a string, not a date/time object. -
You do not need to use
DATETIME_PARSE
because you already have a date/time object. SaveDATETIME_PARSE
for when you want to turn a string into a date/time object. (For example, when converting a text field, or when creating a new date based on a month, day, and year.) -
There is no Airtable formula function to determine the timezone of the client computer.
Here are my thoughts:
-
You are on the right track by having a field for the time zone for each person. I recommend using a Single Select field for the time zone instead of a text field. That way you don’t have to worry about typos in the time zone.
-
You have six date/time input fields: three pairs of start/end times. Can you clarify the workflow that requires three pairs of times as inputs? I would expect that each session would have only one date/time input pair, and that the rest of the fields would be calculated.
-
Consider having a start date/time and a duration as your input fields. Then calculate the end date/time. That makes for is one less date/time input field, and one less field to deal with time zones.
-
Who will be doing the data entry? Will mentors & mentees on their on their own computers? A single a third party in a specific time zone? Multiple third parties in multiple time zones? This determines whether or not the input fields should be set to display local time or GMT time.
-
Some people like to set date/time fields to GMT time for everyone, but then they pretend that the GMT time is their local time. I highly recommend against this practice.
