@Jeff_Hager just one more point to considerā¦ when we take each users local time and use that to determine GMT, we are relying on the fact that all of our users clocks will be correct. Whilst thatās probably generally true - as most devices will be set by default to use NTP - I donāt think it can be guaranteed 100% of the time. And when you are dealing with race conditions, it only takes one user to be a minute or two slow and another to be a minute or two fast - and everything flies out the window.
This again is where I think using an external source as a reference point is key. It doesnāt even have to be GMT, it could be anything - Mickey Mouse time, whatever. The important thing is that itās a single point of reference.
This is fantastic, Jeff! Iāll test with an app that Iām using currently that depends on calculating system time and get back to you. Well done brother!
My results from the UK. Itās correct because weāre in BST right now. This is a great piece of work @Jeff_Hager. I think youāre right in your assumptions about JS and browser capabilities. I canāt think it would be too difficult for @Mark to add to Glide somehow.
I think Iāll have to make some additional updates to account for those Aussies that like to throw time around like they have no wuckas. Geez, 30, 45 (kinda), and 00 minutesā¦really?
This is great. I integrated some services in my app that automatically writes GMT/PST alongside the timestamp that comes back, and I usually convert that to a date/time column like what you did here so it converts to the signed-in userās timezone, it is good.
However it never occurred to me about this offset time, thanks for putting all the work into this.
I am thinking about an Integromat flow where you can use this offset time to do a weekly reminder. My clientās users need to be reminded about doing an in-app quiz and they can choose a date of the week to be reminded. I have done that already, but itās stressing me about a timezone problem that this may fix.
In Integromat, I can only run it based on the timezone chosen in the account (in my case itās Mexico City, GMT-5). If I run that scenario 7am everyday to check for every row in the Sheet and sends an email if the reminder weekday is the same as Integromatās ācurrentā weekday, timezones which at that point are not in the ācurrentā day wouldnāt have their reminder delivered correctly.
Do you have any further thoughts about this use case?
Wow, I guess I didnāt realize that this would work for other timezones. So essentially, if you append the timezone code, such as PST, PDT, MST, MDT, CST, CDT, EST, EDT, etc. or if you append +0, +5, +8, etc. (not sure about negatives or decimals), or better yet, GMT+5, GMT+8, GMT-5, GMT-8, GMT+12:30, etc. after any date/time, it will lock that date time to that timezone, and whatās displayed to the user will adjust accordingly to the userās local time. This could be a huge advantage for creating meetings or appointments that cross timezones as every user would see a time according to their local timezone.
Iād probably need a better visual, but I would think that you may be able to convert any user entered date/time to or from GMT-5., so if I understand correctly, it may work for you.
Every 7am Mexico City time I run through all the rows in the Profiles sheet, then pickout only the ones that has the same āReminder weekdayā as the āNowā weekday of Integromat, then send them emails.
With your method here, Iām thinking about an extra offset column to let me calculate the ācurrentā time of each user, in the Sheet. I would run the scenario every hour to check for rows that have the ācalculatedā time as 7am, for example.
So it comes from sending everyone emails at 7am Mexico City, to being able to send emails at 7am their time.
Yeah, I think that would work. Trying to run it through my head, but Iām sure thereās a lot of parts to it.
So basically you are figuring out the userās current day. I fall into the same time as Mexico City. So for example, I assume you are 12 hours ahead. I get confused with the international date line, but as of right now you are a day ahead from me, but at 7:00am my time, we would still be within the same day. With that I wonder if you would have a problem at all. But ignoring that, lets say your process runs in the next half hour and you picked Thursday as your day. As it stands, it would not pick up your day because Mexico is still on Wednesday. So if you had your offset of +7 hours combined with the -5 for Mexico, you would have a +12 hour offset, so you would need to add 12 hours to Mexico local time and then check the weekday of that calculated date. In theory, I think it should work out pretty well.
Some timezones have daylight savings so the time shifts by an hour, so Iām not sure if that would cause some rare situations where the calculated date would jump by an extra hour, but hopefully that wouldnāt be much of an issue.
@Jeff_Hager, here is my solution for this issue although I think, both solutions complement each other.
In my case, I have a Google Sheet with a timezone XX and I try to have on APP the time based on that GS timezone and not the user timezone which is used by Glide.
If my GS is using āEurope/Berlinā timezone (GMT +01) and my APP has multizone users around the world (USA, Brazil, Spain or Venezuela) buying tickets or making a booking Iām going to need to have transactions timestamp based on my GS (Berlin) always to have a real order and know the sales queue correctly (based on time).
Otherwise, If I use the Glide time (based on device/user timezone) my transaction log will be hard to understand and sort out.
The key in my case is write and use the GS timezone into a cell using the Now() function. This will rule everything regarding time and date. Itās the only thing you will need to use in your GS.
ā¦ .
Later, in GDE you will have these 3 new columns with a simple logic/math:
In my case, Iām in Venezuela and have -6 hr with Berlin and @Mark_Turrellās house .
With it, any transaction done by me (or any user) from APP will be logged using Berlinās time instead of local time (user timezone).
Although few people and in few times the GSā timezone will be changed frequently, I created a choice list to carry out it automatically and test better my solution (using a small script as well). None of you needs to have it on your APP to get this functionality, this is not mandatory.
Again, I created the timezone choice list and my script to make this APP demo more friendly only.
Yes, it looks like the @Simon_Hillās idea but there is one difference:
Simon needs to send any value previously to his GS to update the time and later use it in any operation. If your APP has a Pro plan, it isnāt mandatory because of Glide will update APP from GS each 4-5 min (including everything associated to Now() function).
But even using a Pro plan, Simon still needs to send a value to GS (via an Increment action) to avoid losing up to 4-5 min caused by Glideās automatic update.
My solution doesnāt have this problem with a Pro Plan. Once APP has read the time from cell, the new Glide time is updated every 10 sec by Glide internally and automatically.
Instead, if your APP uses a free plan, you will need to do something like what Simon does but there is a time window about 1 hr in where you still have the synchronized time without problems.
Just to clarify, whatever your plan so long as you change something in the GS then the entire sheet will be updated instantly. I donāt use āincrementā anymore. Instead I use āset columnā and push the current Date/time into any redundant cell. Itās pretty easy to add these ātrip wiresā into any app so that common user actions automatically trigger a GS time update.
Thatās a good solution too. I was going to ask about how the sheet time was updated in glide, but now that makes sense with the background refresh, or setting a value to resync. So in theory, you should never have a time drift of more than 5 minutes, so your rounded offset calculation will always fall to the nearest hour.
Just something to be aware of, there are those rare timezones that differ by an additional 30 or 45 minutes. May not matter much depending on the scope of your app, or where the userās are primarily located, but something to consider.
ā¦ . 
, you can see what happens on my APP verifying several timezones and times:
or 
