Having Timezone issues - Examples within

[andywingrave]

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?

[Mohamed_Swella1]

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

[andywingrave]

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.

[Mohamed_Swella1]

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

[andywingrave]

Mohamed_Swellam:

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.

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.

[Mohamed_Swella1]

That is weird, it is working for me!

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

[andywingrave]

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:

[andywingrave]

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.

[Joe_H]

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!

[kuovonne · Accepted solution]

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.