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? :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

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”

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.

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.

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!

You’re welcome!