Fixing timezones with Google sheets (not glide tables)

[Muhammed_Emir]

Helle Glide Community,

I am sorry if it is duplicate but I am trying to solve the timezone for different users in different locations. I’ve seen solutions and mainly this, but as far as I think this was before introducing the column format date.

System/GMT Time - Solved!

This is an extension of the request for System Time, please. There’s been a lot of questions for a while about how to get a universal time that can be shared for multiple users across multiple time zones. Most of you will know that any time your get a date/time from one of the glide special values, it will always be the user’s local time. But if you don’t know which time zone they are in, then you have no easy way to convert that date/time into something standard, like Greenwich Mean Time, to…

since the checkbox “respect timezones” doesn’t support google sheets and glide doesn’t save the datetime in UTC+0 in google sheets, I came with a walkaround but I would like to ask if it is correct.

my steps are

1. Add a format date column with Z
2. A helper math column that will subtract/add the offset from the format date column (this will be used at submission and will be based on users browsers timezone) with the “X + (Y/24*-1)”
3. On submission I will override the value of date column to the value of the helper math column, this will save real UTC+0 value to google sheets
4. Finally a display column that will use the same format date column to add again the offset (will be used at display) with “X + (Y/24)”

Is there an easier way? and can something go wrong with this setup?
@Jeff_Hager

[Darren_Murphy]

I would (and still do) use the solution that you linked to. It has proven over time to be the most robust and reliable solution.

Regarding your own workaround, as soon as I saw that you are using the Format Date column, I stopped reading. That column has also proven itself over time to give inconsistent and unreliable results.

[Muhammed_Emir]

can you please link me to some cases because I am using it alot in my app and didnt have any problem. but would be horrible if it doesnt work well as I am almost using it in all tables I have

[Darren_Murphy]

That’s the problem with it. It works perfectly fine probably 99.99% of the time, and lulls you into a false sense of security. But when you get bitten by that 0.01%, it hurts (and makes your head explode).

Below is a bit of a rant I had about it last year.

Time Format

The other thing you need to do is test it at different times of the year. Early on, when I was trying to use it, I found that it would sometimes get the day and month mixed up, but only under certain circumstances, and only if the date was ambiguous. For example, today is September 25th. In the US, they write that as 9/25/2022. In the real world, we write it as 25/9/2022. But either way, it’s obvious that it’s September 25. It can’t be anything else, because there is no 25th month. But, le…

[ThinhDinh]

To add to Darren’s point, I don’t have specific examples to show here, but once in a while I have problems with it on iOS Safari, so I don’t use it as much nowadays.

[Muhammed_Emir]

With my solution I am only extracting the local offset and not actually doing date formatting. I will go with it and see what will happen😅

[ThinhDinh]

Yeah, but test it on Safari. Glide has some problems regarding date/time with Safari (it’s the browser’s problem, not Glide).

You can try accessing the builder environment using Safari, you can see if it shows a problem right in there.