Having Timezone issues - Examples within

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?

1 Like

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

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.

Oh so it didn’t work as I said? :smiley:

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

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.

That is weird, it is working for me!

this is my base https://airtable.com/shrulkmiRc8REDSfi/tbloTQNhELV3F00R1?blocks=hide

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:

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.

1 Like

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!

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, and SET_TIMEZONE. Note that DATETIME_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. Save DATETIME_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.

Dealing with time zones is a difficult problem that I have solved. I have created a demo file and a video to accompany the demo file, which I will be posting soon… just editing out some of the parts of the video right now.

1 Like

Thanks everyone! This was super helpful!

Here is my sample base & sample video on how to solve the multiple time zone issue in Airtable:

Wow! The bit I couldn’t find documented was the offset part - This is amazing. Thank you!

1 Like

You’re welcome! :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.