Formula to convert different timezones to GMT?

[Krista_Grenstro]

Hi,
In the base I am working with we have entries representing individual sessions that are held in different timezones. I understand that the sync between Airtable and Google calendar sends over the times in GMT, so I am looking for a way to use a formula to convert the time of the session (for ex: 9:00 SGT) into GMT without having to manually convert it every time we make a new entry.
So, when someone from my team adds in a new session they can add it in as the time it is actually held, ex: 9:00 - 11:00 CEST, and it will convert with the formula in the date + time column to GMT.
Can someone please help me with this?
The main timezones we use are PST, EST/EDT, CET/CEST, SGT, but we use other timezones as well.
Thank you!

[kuovonne]

Airtable stores date/time fields internally as GMT. Are you using date/time fields and how is the “use same time zone” toggle set for your fields? If you are not using “use same time zone”, everyone should see the values in their local time zone and you can use formula fields to display the date/time in other time zones.

[Krista_Grenstro]

Thanks for the quick reply! So let me try to explain a little more. So if we have a session that is held 9:00 - 11:00 CEST, and we have the calendar synced with google calendar, and all of my colleagues have added that calendar link in their google calendar.
For me who is located in Stockholm/CEST, in my calendar it should show as 9:00-11:00. For my colleague in New York/EDT, it should show in their calendar as 3:00-5:00.
Right now, we enter the session time into that date + time column, and have the GMT option ticked in.
But Airtable thinks that session is GMT time, so it is showing in my calendar as 11:00-13:00.
I’m looking for a formula where we can still enter the actual session time in one of the columns, and that will convert to GMT time in the Date+Time column. I just can’t figure out how to do it.
Do you follow me?

[kuovonne]

It sounds like your “use same time zone” toggle is set to the opposite of what I would envision. I suggest you try a different setup where you do not have everyone using the same time zone. That way times would show up as local times for everyone everywhere—in Airtable and Google calendar.

Trying to manually convert time zones is difficult. You can try to fake it by using DATEADD to add/subtract hours but then you run into problems with things like daylight savings time.

[Databaser]

@kuovonne, maybe a good idea for a new app? :slightly_smiling_face: Selecting a field with a date/time and being able to select the current time zone to see a selection of other time zones?

[kuovonne]

An app could be useful, but developing custom apps is a lot of work.

You can already create a formula field that will show a date/time in a specific timezone. The Ready Made Formulas app makes it easy to create the formula.

The most common use case is when a company has teams in multiple time zones. The base has a regular date/time field set to show events in local time (but are really stored in GMT internally). Then there is a formula fields for each time zone to show the time at each time zone. This way each team can see the time of the event in local time (using the editable field) and the time of the event for the other team (using the formula fields).

I believe that Krista’s situation is different. She has been entering a “local time” in a field that is set to display GMT. However, this has big problems with other time zones, because Airtable does not know the time zone of the original data entry. When converting to other time zones, the result is off because Airtable doesn’t know that the internally stored GMT time really should be the time at a different time zone…

I highly recommend never storing a “local” time in a field set to display GMT.