I’m on the struggle bus with time zones in a booking system. It hasn’t clicked. And I’m pretty novice with formulas in Airtable in general.
I started off “wrong” so I’m trying to adapt formulas in a system that’s already live. I can’t simply start over.
Basically, I’ve got folks from all over the globe signing up for 30 minute time slots. I had made Offical Time of Event MY time zone, thinking AirTable would be smart enough to convert to the user’s time zone and back to mine (like Outlook does).
Once I realized that it doesn’t, I tried applying formulas that I don’t understand. Basically, copy and paste because I need to understand before I can truly apply. I want to understand! Especially because copy and paste isn’t working.
My booking system is showing my time and the conversion to GMT OK, but User’s Local Time is horribly off, especially in the empty slots.
The formula I’m using in User’s Local Time of Event stands at
IF(AND({Official Time of Event},{Official Time Zone of Event}),DATEADD({Official Time of Event},-{Time in GMT},‘hour’))
Time zones are nearly impossible to work with in Airtable, as @Cherry can testify to!
I see that you downloaded my time zone base to use as your starting point, but you might have missed the training video that goes along with it.
If you hover over the little “i” button (the “information” button) that appears in the column headers for 4 of the fields in my base, you will see that there are some important notes that appear.
The notes help to explain that it’s not just a matter of getting all of the formulas right, but it’s also a matter of getting all of the “GMT Time Zone” toggles right as well!
Compare your base to my base, field-by-field, and see if that makes a difference!
I see the "i"s and I did watch the video. Did I mention I’m on the struggle bus?
In the Official Time of Event column, I don’t have a “GMT” option to turn on, per se. Are you saying I have to use GMT - period? I can’t simply adapt the formula to convert from America/New_York?
In your example, is Official Time of Event in Official Time Zone or GMT? I’m so confused. If I turn my Official Time to GMT that will change all my times? I’m so confused.
As for your issue in Airtable, who is actually using your base? Just you or also the people around the world who are also booking calls with you?
If it’s just you, then keep the timezone set to NYC is fine.
If you need people from all over the world to use it, you can either let everyone know this time is in NYC only or have everyone use Airtable in their local time.
For date/time fields, have Airtable show the local time and display the time zone.
In some cases it makes sense to have Airtable always display the time in a particular timezone.
Date only fields always have a time of GMT midnight when used in formula fields.
Regardless of the timezone used to display the date/time in its own field, under the hood the date/time is stored in GMT and will be in GMT when used in formulas unless you convert the time zone using SET_TIMEZONE(). Typically only do this when converting the date/time to a text string.
If a formula field returns a date/time object (not a text string that looks like a date/time, always check the formatting options for setting the timezone. If you don’t have the formatting options for a date/time, your formula doesn’t return a date/time object.
Avoid setting a field to use GMT for everyone but then treating the displayed time as local time. This will mess you up if you try to do any sort of integration with other services. This is also completely unnecessary now that Airtable allows you to set a timezone for a date/time field.
Prefilling times in a form is a mess. Avoid if at all possible.
I’m using a form to collect times for people who do not have access to the base. My form doesn’t display the time zone at all – despite the fact that I’m pulling the information of available time slots.
I could just say all times are Eastern, but then the math is on the user – and math is hard when folks are in Australia or the Philippines.
How do you avoid prefilling times in a form? What’s the goal there? Pick a time by email seems similar.
Oh damn, I forgot that they changed the description in the formatting options. The “same time zone” toggle used to say the word “GMT” there because it was ONLY GMT time zone, but now there’s a popup menu to choose the time zone.
So yes, what you will want to do is enable the “same time zone” option and choose “GMT/UTC” from the popup menu. Do this for any fields where I previously said to “turn on GMT” for those fields.
At some point, I’m going to need to go back and update my training video & my base instructions!!
p.s. In general, I’m not 100% convinced that Airtable is the world’s best system to use for handling events that need to be accessed by users across multiple time zones. Time zones were handled much more elegantly in FileMaker, but that is a full-blown programming language that requires working with a Certified FileMaker Developer who can write all the programming code for you. That is what I used to do before becoming an Airtable Consultant! I can still refer people to other top-notch FileMaker developers, if they’re interested.
It can sometimes be easier to use an online appointment scheduling calendar app like Calendly, and then use Make.com to send all the data into Airtable from there. Of course, that won’t work for every use-case scenario.
I think next time I will explore Calendly. We already pay for Airtable and I’m comitted to understanding it. Give me 9 years… at which time everything will completely change on me… but I digress.
Back to your example, Scott. Official Time of Event is set to GMT, but the time used is, say, the Los Angeles Event time? We’re essentially lying to Airtable by saying, yeah, we’re using GMT, but actually, the Los Angeles Event is at 8:30 a.m. Pacific Time… and that’s why there’s an Actual GMT time of Event, where we fess up to the lie in the “Offical Time” column?
While not strictly for Airtable, I wrote a guide on how we manage the three different date-type fields in Noloco.
The way I see it there are 3 anyway:
Date fields (without time)
Date & time fields with the current user’s timezone
Date & time fields with a fixed timezone
I give examples of when you would choose each.
For your booking example, I would just use a standard date and time field, because Airtable will present the input to the user in their local timezone, and you will always see it in your local timzone.
Anything else is generally incorrect.
The rule of thumb is
You should only really specify a timezone when the location of the event is important, like an airplane taking off, or if you are 100% certain nobody from another timezone will need to see the date field, but even then you’d be better off with the standard one.
The odd exception is if you want to store dates in UTC/GMT format which is handy for integrations, but when you use it in an integration it will be in this format anyway.
