Help

Re: Choosing the time zone used for Date and Time functions

Solved
Jump to Solution
3138 2
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
Chinara_James
6 - Interface Innovator
6 - Interface Innovator

I’m not sure if this helps but when using time formulas, if you clicking on the formatting tab you can uncheck use " Use the same time zone (GMT) for all collaborators ". This will display your local time.
uncheck-option-use-GMT.png

That did it for me. Thanks Chinara.

The problem is that this is not using my local time and I need to set my account to use Central (Chicago) time. It is 2 hours off. This is a bizarre design flaw. When integrate to an external calendar App my info is not accurate. ??

Yawnxyz
5 - Automation Enthusiast
5 - Automation Enthusiast

We should really need to at least know what time zone the time in a column was created… I’ve been traveling a bunch and it’s next to impossible to figure out if the time value has changed or not… kind of forces you to use a Single line text/String to track time instead of the time field type, since you can’t really trust it’s showing you the right value.

For example, if you set an agenda (9am registration opens) and then you travel to another time zone, it magically says the registration opens at 8am… but in reality, you always want it to be at 9… it shouldn’t depend on where you are. Yikes! And as someone said, using GMT isn’t really helpful either :frowning:

Sarah_Smith1
4 - Data Explorer
4 - Data Explorer

Looking through this thread and it’s been going on for years… it’s now 2019. When is this going to be fixed? Having to create a formula field to try to adjust isn’t a solution. I can hardly figure this out and my co-workers I’m setting up definitely can’t. I have this linked to send an email when something we need to do starts on that day but it’s sending it to us the day before that because GMT is so many hours ahead. How can I fix this? I need it to be in PST.

Can you describe more about the system you’re using for this email notification? My guess is that you’re tying in to Zapier or Integromat, in which case you’ve got one additional place that you need to set your time. I don’t know for sure about Zapier, but Integromat user settings include an option for specifying which time zone should be used as the default for time-based operations (like triggering a scenario to run). To find that setting, click on your user button at the bottom left when logged in, choose Profile, then select the Time Zone Settings tab.

EDIT: Found a similar setting in Zapier, at the bottom of the profile page.

Hi, I am using zapier and the time zone is already set to Los Angeles.

R_Scott_Lord
5 - Automation Enthusiast
5 - Automation Enthusiast

I am having a similar problem. I am loading an Airtable base from Zapier (timezone is set to Anchorage). It appears that Airtable is resetting the time/timezone when ever the record gets created. I can verify Airtable is changing the date/timezone because the Zap sends a text with twilio as the next step in the Zap and the time is correct. Date and Time seem pretty easy, but it is actually an extremely difficult problem to implement. I agree, it is time (haha) for Airtable to step up and tackle this problem, it will be 2020 soon and this thread has been going on since January 2016…

The problem with DATETIME_FORMAT() is it changes the data type from date to string. I haven’t been able to find a function that converts a string into a date data type.

See DATETIME_PARSE(). It uses the same format specifiers as DATETIME_FORMAT() but converts a string to a datetime value.

The wide range of input and output formats supported allow for all sorts of interesting datetime hacks. Two of my favorites can be found in this reply.

I changed my Airtable timezone setting to “Use the same time zone (GMT) for all collaborators” and know AT is using the correct date the zap is delivering, the only problem is AT thinks it is GMT, but it is actually Anchorage time. I could change my zap system time to GMT, but the transaction time I am loading into my base would have to be converted to GMT from Anchorage, where and how would i do that? This is a lot of mental gymnastics… It sure would be nice if you could set the timezone in the base preferences…

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…

@Sarah_Smith1 Does any of the above help in your situation? I’ve been too busy lately to dive in for a deeper look.

Just to clarify, I’m not an Airtable employee, so I can’t speak to how/if the team is addressing this issue.

Thomas_Rush1
4 - Data Explorer
4 - Data Explorer

Stiiiiil nothing? It’s the year 2247 and Airtable hasn’t fixed their timezone bug.

Is this still the best solution? (over 4 years later)

ES1
4 - Data Explorer
4 - Data Explorer

DOES ANYONE WHO WORKS AT AIRTABLE KNOW ABOUT THIS ISSUE?

We can’t ask people to convert a date to GMT when they enter it. That makes no sense.

And we can’t have people look at an event date in their own timezone, bc that makes maintenance of event times virtually impossible.

And this makes the calendar sharing virtually useless, since when we import the calendar into google calendar, etc, it shows all the wrong dates.

AIRTABLE PEOPLE, PLEASE HELP.

Hi,
the workaround I’ve found is to:

  1. create a new field (say DATE OK);
  2. Make it a formula field
  3. Use this kind of formula (this one substract 4 hours to GMT which seems to be the default Timezone in Aritable): DATEADD(Date,-4,‘hours’)
  4. Adjust the format at will.
    Hope this help!
    Cheer
    s

I’ve created a sample base & accompanying training video to solve the dilemma of working with multiple different time zones in Airtable.

You can click on the link above to be taken to my solution in the Airtable Universe, or you can go to my post for this topic here:

Hello again @ScottWorld and other AT experts. I see a lot of other ATers have had this issue for years. I am using the table all by myself atm, no. collabs. Is there a way to have the base/table use my local time zone (PDT) without having to update each field/formula with a DATETIME wrapper, or using a new base (referenced here)?
As always, appreciate the help and guidance.
Regards,
Sunny

When manually entering dates, Airtable’s default behavior is to leave them at your local timezone (according to something I recall hearing from someone at Airtable). Only when formulas come into play do things get messy. For one, the formula field’s “Formatting” tab defaults to displaying dates based on GMT, and you have to switch it off manually. That’s one thing I have to keep reminding myself when setting up new date formulas. Also, using either TODAY() or NOW() can be tricky because TODAY() is based on GMT, but NOW() is based on local time (from what I’ve been told).

Is there a specific issue you’re trying to address? If so, it might be best to start a new thread for it.

Wow!! I did not know that!! That is a hugely important thing to know about, and it’s not even documented on their formula field documentation page. You’re right — the NOW() function always returns a value based on the user’s CURRENT TIME ZONE, but the TODAY() function will always return a value based on the GMT time zone. This is a huge difference.

@sunny As Justin said, if you don’t turn on the “GMT” option, Airtable will continually update the time based on whatever time zone your computer is set to at the current moment.

So, if you’re in PST right now and you type in the time of 8:00pm, the system will always remember that time as 8:00pm PST. But, the way that Airtable works is this: If you then log in from EST, the time will then display as 11:00pm. If you have another collaborator working in EST and they type in the time of 8:00pm on their end, Airtable will remember that time as 8:00pm EST, so if you’re in PST, you will see that time as 5:00pm.

Yes, it’s strange and it’s unexpected. But once you know that Airtable works this way, you can accommodate for it.

All of this is covered in depth in my training video & sample base. In my training video, I even change the time zone a few times on my own computer to show the viewer what happens to the time fields.

To keep times consistent at all times, you really only have one option, which is to enter in the times as GMT times (by turning on the GMT option).

Then, if you need to translate the GMT times into different time zones, that’s where my sample base comes in. You’ll just need to add a few extra fields to your own base (based on my base), which will let you explicitly translate the times into different time zones.