Choosing the time zone used for Date and Time functions

[Chris_Rutledge]

I am in Seattle, using my local time zone, Pacific Standard Time (GMT -8). One of my columns, ‘Start Time’, contains a Date value of “1/2/2016 10:00”, but when I reference it in a separate column using

DATETIME_FORMAT({Start Date}, ‘MM/DD/YY HH:mm’)

the time displayed is 8 hours ahead (18:00). The same thing occurs for any other times I reference.

I see that I could use SET_TIMEZONE, but do I need to use this whenever I want to have the results of a formula in my time zone? Is this a setting I can change in my account or base, anywhere?

[Justin_Barrett]

Welcome to the community, @Alix_C! :grinning_face_with_big_eyes: When entering manual dates, they’re always treated as local. The hiccup arises when using formulas to process (or even format) those dates.

[Jess_Databases]

+1

With a team located mainly on the East coast, it would be great to be able to deal with times in ET, instead of having to convert to UTC when inputting times.

Having each user see time on their own time zone is not possible, because we reference events by the time and would lead to too much confusion.

[Sean_Lake1]

Our team works in all different time zones, and we schedule courses for clients in several time zones as well, so we wind up needing to be able to have the times adjust to their time in the fields.

[ScottWorld]

For a deep dive into working with Airtable time zones, check out my sample base & demo video here:

Airtable

Time Zones: Working with multiple different time zones in Airtable - Airtable...

This Airtable base solves the problem of events taking place in different time zones, and users wanting to know what time those events are taking place in...

[Andrew_Heine]

This solution only works if you want to display in a single timezone for all viewers. I have collaborators across the world and I need it to dynamically display the date/time in each of their respective timezones. Any ideas how to do this?

[Justin_Barrett]

I don’t believe that this is possible within Airtable, at least not directly within date fields themselves. Dates are stored with timezone data baked in, and display relative to a) the timezone of the current user or b) GMT, depending on the field formatting settings. There’s no way to customize date display in Airtable based on viewer location or preferences.

The main workaround that I can think of—which may not be feasible depending on how many collaborators need this option—would be to make custom formula fields that adjust important dates for specific timezones, along with custom views for each collaborator that show the relevant formula fields. Naturally there are downsides to giving collaborators read-only dates, but in some situations it might work.

This might be possible via third-party dashboard systems like Stacker, miniExtensions, etc. I have very little experience with such systems, so I can’t say with confidence whether or not such features exist, but IMO an ideal dashboard tool would allow each user to indicate their local timezone and auto-convert Airtable dates for display within the dashboard based on that setting.

[kuovonne]

Justin_Barrett:

Dates are stored with timezone data baked in, and display relative to a) the timezone of the base owner or b) GMT, depending on the field formatting settings.

Dates and times either show in GMT for all users or in the timezone of the current user, depending on the settings for the field. The timezone of the base owner does not matter.

[Justin_Barrett]

Thanks for the clarification. I’ll correct my earlier comment to reflect this.

[Justin_Barrett]

@kuovonne If that’s the case, then there’s no workaround needed for collaborators to see dates in their local timezone, correct? (I never work in bases with collaborators, so anything collaborator-related in Airtable is immediately a bit of guesswork.)

[kuovonne]

If we are talking date/time fields (and not formula fields), and if the field is not set to use the same timezone same for all collaborators), then the date/time displays in the local time zone in the user interface. I have tested this with multiple collaborators across different time zones.

Note that the local time is the timezone set for the computer, regardless of where the person is physically located. For example, if a person travels to a different timezone but does not change the settings on the computer (and the computer does not detect the change), “local” date/times will still be for the original pre-travel timezone. I have also had a client test this.

It gets a little tricky as soon as you talk about date/times in formula fields.

If you are using only DATEADD(), then the same rules for timezones apply as for regular date/time fields. The settings for the field determine the timezone displayed.

If you are using DATETIME_FORMAT or DATESTR(), then things change. The resulting formula will have GTM time, unless you also use the SET_TIMEZONE function.

TODAY() and NOW() also have their own unique brand of strangeness relating to timezones and how frequently they are updated.

Things get even trickier when a person wants to see a date/time as it will appear to a person in a different time zone. For example, say you have an event {Start DateTime} and it is set to show in local time. If you are in New York, you will see the event start time in New York time. But if you want to see what time it will be for your client in Los Angeles, you will need to use DATETIME_FORMAT with SET_TIMEZONE for Los Angeles.