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!