Display date/time in local user's timezone

I have read a few posts about Date/Time formatting, but my issue is more about displaying the local date/time instead of the “server US” time.

Here is an example.

When a purchase is made, a date/time entry is added to the sheet, as highlighted below.
image

When I format and display the time it’s displayed in the app as such, 2:06AM which clearly is not early in the morn here in Aus :grin:.

image

The spreadsheet settings are already set to my local time

image

some additional info, I have a work-around to using Glide’s cart feature to identify all purchased items in a single order (unique and the same for all items purchased in the order) by looking at the recorded date/time in the App:Sales as per above screenshot. And I use the last 6 char as the order reff eg; 08.625Z

so I don’t want to reformat the cells, but rather simply display the recorded date/time by Glide which appears to be US and convert that to my local time.

I would probably create another column with a formula to add or subtract the number of hours from the timestamp, assuming that the timestamp in the sales sheet is consistent as far as using UTC time or a single server time.

@Mark or @david what timezone is the timestamp added to the App: Sales sheet?

I need to calculate the local timezone based on that.

Hello,

I have different time zones & times.
How to display those times as local time for the user( at his time zone)
Is there a format I can use?
@Jeff_Hager
@Mark

Hi Leon,

Probably you will have to get the user’s timezone info in a column of the user profiles sheet, then based on the timezone that was captured in the app for each of your events, calculate the difference between those timezones and add/subtract the time accordingly.

Your sheet’s time.

get the user’s timezone info in a column of the user profiles sheet
How do i do that?

Hello @Leon_Kol ,

You could allow each user in their profile to choose their timezone manually from a list.

Then you just have to offset their time from your sheets local time (your time) and then there time will be available.

Please note that unless you have your sheet updating automatically (paid plans) your users will have to manually trigger a refresh to show the current time (any update to the sheet from glide will do so). I have my time shown in an action text component with the title “Current Time (tap to refresh)” with a configured action to simply increment a “refresh” cell on my sheet. This causes the app to pull the new time data.

I just went to wikipedia and grabbed the timezone table, then in each of the users rows I added a time and timezone field. The time takes the sheet time (from somewhere in my sheet) and adds/subtracts the timezone choice (adjusting for my timezone not being UTC).

Please let me know if you have any questions, I have put the above together and it is working.

Good luck!

1 Like