Automatic Time Zone Conversion without a lookup table (Solved)

I’ve been thinking about it all wrong.
The wrong way is to try to have a some distant time that you want to show in your local timezone and try to use a lookup table to do the time offset.

Glide uses the local time zone for each user regarding the Now() formula in the data editor. This can cause app configurations that relies on the now feature to be completely off when users are in different time zones.

Example. I have a meeting that starts a 9am est and I have the app set to reveal the details for the meeting at 9am however its 8 am in central time. How do I get the app to unlock at both 8am central time and 9am eastern time without needing a lookup table to search and find time offset.

The user in the other time zone is actually the offset all we need is to is to find a way to reverse calculate their time difference between the google sheet difference and it will produce their time offset automatically.

Once we have the time offset we can now use the offset (for each user), to offset their times automatically. Even if the user were to travel to a different time zone and use the app, they would create a different offset according to the difference between glides clock and the google sheet. So their offset is dynamic.

So how do we get this dynamic offset that follows each user.

  1. For each user row in google sheets you will need a running Now() function coupled to an arrayformula. Make sure your sheet is set to update automatically with change and every hour.

  2. you will need the user to drop their Now time () from glide to the googlesheet by some push of a button. Note: you can use multiactions in glide for each button the user triggers to update a row. (For my app I just have the user click the edit pencil to their profile and close it, I have the forms automatically set to punch in the Now() in the background every time they edit their profile. You can get creative and find other ways you can have the user drop their Now() into google sheet.

Their now time from glide looks real ugly
( 2021-01-11T19:27:07.463Z)and requires some cleaning up before google can recognize it as a time. Here’s the cleanup code. You have to replace it with your relative cells (


=Arrayformula(IF(AR2:AR="","",Substitute(Left(Right(AR2:AR,Len(AR2:AR)-5),5),"-","/")&"/"&Left(AR2:AR,4)&" "&Left(RIGHT(Right(AR2:AR,Len(AR2:AR)-5),Len(Right(AR2:AR,Len(AR2:AR)-5))-6),Len(RIGHT(Right(AR2:AR,Len(AR2:AR)-5),Len(Right(AR2:AR,Len(AR2:AR)-5))-6))-5)))

AQ = EVery user’s Now() Running
AR = When the user hits a button it updates this time (I have them just open their profile and select done, this is done in the background) Creates an ugly time drop.
AT= Glide time cleanup formula for making google recognize the format.
AU = Dynamic Offset. Now we subtract the google static time deposit from the glide time deposit and use subtraction to find out the time difference between the two. This is unique for each user since each user will have a different time gap according to their time zone relative to your google sheet.

Each time the user click the (secret button) knowing or unknowing, it recalculate their time offset.


I also used a rounding feature in google to make sure that slight minute variations betweeen google 5 minute update and glide doesn’t cause a timezone shift. This will only create an offset if the difference is greater than 30 minutes however I have mine set for an hour.
Here’s the formula:
=Arrayformula(IF(AS2:AS="","",IF(AS2:AS-AT2:AT<0,Mround(AS2:AS-AT2:AT,"-1:00:00"),Mround(AS2:AS-AT2:AT,“1:00:00”))))

Hint:
Because Google Now() and Glide Now () are both running we have to make them stand still. So I converted the running times into static times.

Now that you have the offset number for your user on their profile row, you can easily pull it in when you need to off set a meeting time. This will cause the app to display everyones meeting time according to their local time zone.

Caveat.
You have to find creative ways to have the user hit a button in the app that will knowingly or unknowingly deposit their Now() on their profile row.

IF the user doesn’t drop their now() into your googsheet, this wont work. this shouldn’t be hard to do since users have to update their profile with phone number pics etc. All you have to do is add an automatic drop for a Now() into the sheet so when they select done in their profile update the Now() will get dropped also.

image

I have the users offset live for each logged in person which will give a customized offset according to their deposits Good Luck.