Identify Time Zone

Morné Theron

Hi;

I have a sheet that users captures information in, I have a column that records the modified date time, so the column is type of modified.

Example Sheet1:

Descrption , value , ModifiedDate

abc , 123, 2024-01-01 10:50 <--- date time shown in Sheet ex GMT+2

Example Sheet2:

Item Name , Value, ModifiedDate

abc , 123, 2024-01-01 14:50 <--- date time shown in Sheet ex GMT+2

I have a overview sheet that lists all the manual input sheets and a column that then looks up the last modified date in a row from the manual input sheet.

Example manual infoSheet:

SheetName , SheetID, Last Modified Date

Sheet1 , 89789539, 2024-01-01 10:50

Sheet2 , 79789539, 2024-04-01 14:50

If i read sheet1 and sheet 2 via the API we will get the Modified columns values in UTC date.

But reading the infoSheet column (via API) being in text will return the time in the time zone it was captured in.

So if the users are in GMT+2 zone, and the modified date was :

2024-01-01 10:50 and when we extract this UTC time will be 2024-01-01 08:50

as mentioned above when reading a column of type Modified the API will give me 2024-01-01 08:50.

However the overview sheet because being a formula to get the last modifed date it is thus stored in text.

1. How can I have the info sheet show me the actual UTC time and not the time in the Time Zone which the API user is in.
2. somehow store the time zone to know how to calculate the UTC time
3. Get the Modifed date and store it in a column in the infosheet that will also be stored as the same data type as the modified column so that when the API extracts this sheet it will already be in UTC date time

A.J.

This is a very interesting question! I'm interested to see if anybody can answer!

Brian_Richardson

As you stated the API returns date/time in UTC. There isn't time zone information returned for a sheet, because sheets don't have time zones. Users have time zones.

If you have a target user in mind you can Get User to find out their time zone and then run a conversion on the UTC data to take it to the time zone that you want to store.

As you noted, if you pick up the Modified date into another column that's a text field, then you've lost the UTC information entirely and won't be able to manipulate it as a date. But you shouldn't need to do that… you can cell link the Modified date from one sheet to another and have the destination column be a Date field to maintain the information in a Date format.