Skip to main content

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?

+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.


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.


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




Look at the second example in this reply. You can use a format specifier to extract a value you can then feed directly back into DATEADD() in order to restate your local time as GMT. I agree, it gets confusing when working with an integration timestamp. In your case, I think I’d load it into a non-GMT field, restate it into GMT, and use that value in any calculations…


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?


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?


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.




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.




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.


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


@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 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.)


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.


Somewhat related - the Timeline view doesn’t correctly show the ‘today’ line as it appears stuck to GMT as opposed to the local user time - is there any way at all to change this? You can’t alter this by formula etc…



This seems like a huge oversight for Airtable? I can’t knowingly share this externally to my partner offices with te caveat that the day is wrong!




Somewhat related - the Timeline view doesn’t correctly show the ‘today’ line as it appears stuck to GMT as opposed to the local user time - is there any way at all to change this? You can’t alter this by formula etc…



This seems like a huge oversight for Airtable? I can’t knowingly share this externally to my partner offices with te caveat that the day is wrong!




This definitely looks like a bug. I recommend reaching out to Airtable support directly (support@airtable.com).


I think I have worked out a solution for this:


When you input a time, and that time is using GMT (same timezone for all collaborators) that is fine.


When you input a time and it is not the same timezone for all collaborators it DISPLAYS in your timezone (and the timezone of any collaborators using that field) but it is STORED in GMT.



This means that any calculations are going to be using the GMT time.



This is important because any calendars, Gantts or timelines will be using this ‘machine-readable’ time that is stored. You will be looking at the time as (in my zone) Australia/Brisbane which is GMT+10. Airtable sets that back 10 hours in reality. So if something is at 8am, airtable displays it on the Gantt / Calendar / Timeline as being 10pm the previous day.


This looks clearly ridiculous.



There is a hack to make this work however and it involves you changing how you operate on airtable. If you got this far you can probably figure it out by now.



Yes, it means using formulas to change times from the GMT into a specific timezone and encoding the formula’s output to display a machine-readable time/date.



Once you have that down pat you can start manipulating dates to show what you want and hide those fields so users dont get confused.


Hi Chris and Jack,



We don’t currently have a global setting for the base. You can work around this by either using DATETIME_FORMAT(SET_TIMEZONE(date), format) or you could check “Use the same timezone for all collaborators (UTC)” on date with time columns. In the latter case all times will be stored as UTC.



We’ll consider adding a global default timezone per base.



Alex


Any chance to have this implemented one day?


I think I have worked out a solution for this:


When you input a time, and that time is using GMT (same timezone for all collaborators) that is fine.


When you input a time and it is not the same timezone for all collaborators it DISPLAYS in your timezone (and the timezone of any collaborators using that field) but it is STORED in GMT.



This means that any calculations are going to be using the GMT time.



This is important because any calendars, Gantts or timelines will be using this ‘machine-readable’ time that is stored. You will be looking at the time as (in my zone) Australia/Brisbane which is GMT+10. Airtable sets that back 10 hours in reality. So if something is at 8am, airtable displays it on the Gantt / Calendar / Timeline as being 10pm the previous day.


This looks clearly ridiculous.



There is a hack to make this work however and it involves you changing how you operate on airtable. If you got this far you can probably figure it out by now.



Yes, it means using formulas to change times from the GMT into a specific timezone and encoding the formula’s output to display a machine-readable time/date.



Once you have that down pat you can start manipulating dates to show what you want and hide those fields so users dont get confused.


@Sean_Wilson @kuovonne HI hi!



I’ve gotten to a point with all formulas working properly to get me GMT.



My blocker is that I have trainers inputting availability on their calendar and then project managers selecting a timezone in a “trainer availability” interface.



What I need to figure out is for my final date/time formula to convert my GMT date based on the timezone SELECTED in the interface…



Thoughts?


Airtable has updated its date & time fields since I created this time zone sample base & training video, but this still might be helpful for some people who are struggling with time zones in Airtable.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 


Updated: Saw that you have to wrap the formula in the DATETIME_FORMAT function. Thanks all.


DATETIME_FORMAT(SET_TIMEZONE(CREATED_TIME(), ‘America/Chicago’), ‘M/DD/YYYY h:mm A’)



That would be great. I am having a struggle using SET_TIMEZONE on a FORMULA field. The following doesn’t change the timezone. I might be missing something. Thanks.



SET_TIMEZONE(CREATED_TIME(), ‘America/Chicago’)


Lucky I found this post. I was having trouble reformatting my date/time to something more friendly via a formula column. For anyone slightly confused about how to add timezone specification, the bold & underlined portions are what I had to add to make it work. 

DATETIME_FORMAT(SET_TIMEZONE({your referenced column}, 'Australia/Sydney'),'LLLL')
  • Swap out 'Australia/Sydney' for your desired location

Reply