Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Choosing the time zone used for Date and Time functions

Topic Labels: Dates & Timezones
Solved
Jump to Solution
50736 66
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Rutledge
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

66 Replies 66
Thiefsie
4 - Data Explorer
4 - Data Explorer

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!

image

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

Sean_Wilson
6 - Interface Innovator
6 - Interface Innovator

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.

Any chance to have this implemented one day?

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

ScottWorld
18 - Pluto
18 - Pluto

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 

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