Help

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

Solved
Jump to Solution
2615 0
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
sunny
6 - Interface Innovator
6 - Interface Innovator

Thank you, @Justin_Barrett and @ScottWorld. My table has past, present, and future dates. I am sorting to have records with today’s date on top. Couldn’t find another way to do this so I have an IF TODAY() = 1, 0 condition; and I am then sorting descending 1 --> 0. This works fine except when it’s evening time and AT thinks it’s the next day (a few hours too early) for me.
I use this table alone right now (while still figuring out some kinks) and other collaborators in the future will also be in my timezone.

Switch your formula to NOW() instead of TODAY(), and also be sure to turn OFF the GMT formatting option for all of your date fields… including the formula field.

sunny
6 - Interface Innovator
6 - Interface Innovator

Thank you, @ScottWorld. Switched to NOW() and made sure GMT is turned off in the date field. Did a system time change and it still works. Will report back tonight in case it breaks. Thank you! :smiling_face_with_halo:

sunny
6 - Interface Innovator
6 - Interface Innovator

@ScottWorld sorry for being mia past few days. The change to NOW() didn’t work :frowning: It’s 6.45p here and IF NOW() = 1, 0 already turned to 0

That’s not a valid formula. What are you trying to test for in your formula?

Even if you had a valid formula that looked something like IF(NOW()=1,0,1), that wouldn’t make sense either, because it would always result in 0.

@ScottWorld I think that’s what @sunny is trying to do. As you said, that’s not the way to go about it.

@sunny Based on the formulas you listed, it seems that you’re somehow under the impression that both TODAY() and NOW() are designed to automatically check elsewhere in the record to see if it matches the current date, but that’s not the case. They both return the current date. The difference is that TODAY() arbitrarily throws midnight on for the time (assuming you only want to compare the date), whereas NOW() returns the current day and time at the point it was executed. On top of that, the date and/or time returned for both is based on GMT (contrary to what I told you previously, @ScottWorld. I’ll dig into the depths of this another time.)

Long story short: your formula needs to compare the date in your date field against either TODAY() or NOW(). However, don’t compare them as actual dates because of the GMT difference in those two functions. The easiest way I’ve found is to compare the formatted dates. Specifically, the formatted version of your manually-entered date—which will always be based on your local timezone—against either TODAY()/NOW() after forcing them to your local timezone using SET_TIMEZONE(). You can also skip the IF() wrapper, because the comparison itself will return 1 or 0 depending on whether the formatted dates match (1) or don’t (0):

DATETIME_FORMAT(Date, "L") = DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "L")

Obviously replace the timezone reference with the one for your local area.

Now, if you want to avoid an error if you haven’t entered a date, I do suggest tossing an IF() wrapper around it, forcing the output to 0 if there’s no date.

IF(Date, DATETIME_FORMAT(Date, "L") = DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "L"), 0)
sunny
6 - Interface Innovator
6 - Interface Innovator

Sorry for being lazy, the formula is IF(DATETIME_DIFF(NOW(),Date,‘days’) = 0, “1”, “0”). This works fine (returns a 1) till 4.59p Pacific. At 5p (GMT 12am), it returns a 0 for everything except tomorrow’s records.

I saw you were writing your post while I was writing mine. Long story short, I suggest using one of my formulas above. Comparing date differences still won’t fix the fact that NOW() is based on GMT, whereas your {Date} field is based on your local timezone. The only ways to fix the time shift compared to GMT is by formatting, or manually shifting NOW() by a certain number of hours to convert it to your local time before comparing to {Date}.

sunny
6 - Interface Innovator
6 - Interface Innovator

@Justin_Barrett Thank you! This works :raised_hands:
IF(Date, DATETIME_FORMAT(Date, “L”) = DATETIME_FORMAT(SET_TIMEZONE(NOW(), “America/Los_Angeles”), “L”), 0)

Alix_C
4 - Data Explorer
4 - Data Explorer

I have a simple ask when I create a date field. It should be my local time ie Central.
How can I achieve it? No formula as I was a selectable date widget on the cell. This is where, date is being selected for the very first time.
If the timezone is an account-level setting point me out in the right direction. Thanks

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
6 - Interface Innovator
6 - Interface Innovator

+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
8 - Airtable Astronomer
8 - Airtable Astronomer

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:

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.

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

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.