Calculating date/time on "Created" and "Modified" column types due to API time zone issue

kbloch

Hello,

We are needing a document to generate with our local time zone based on the "Created" and "Modified" date/times. This used to work correctly, however for some reason the past few weeks it's now generating with UTC instead. We need it to generate with UTC-8. It looks correct on the sheet, but the generated document adds 8 hours due to the time zone.

I tried using a DATEADD function but this does not seem to work because I cannot set the new field as "Date/Time," only "Date." I cannot seem to get anything but an output of #UNPARSEABLE. Any suggestions?

jmyzk_cloudsmart_jp

Hi @kbloch

As noted in the Smartsheet help article below, the "Created" and "Modified" system columns display the time in the local time zone, but in the back-end (including formulas and API calls), they are based on UTC:

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

Key points about system columns and time zones:

• The displayed value matches the time zone of the last person who accessed the sheet.
• The back-end value (used in formulas and API calls) is always in UTC.
• This can cause discrepancies when exporting data or generating documents, as they may reflect UTC instead of the local time.

For example, in the demo sheet below, when you apply the DATEONLY function to the Created column, the function will return yesterday before 9 am but will return today after that because local time in Tokyo is UTC -9.

https://app.smartsheet.com/b/publish?EQBCT=1ccd333fe6bc4550b63ffdfa275cffa2

Solution: Adjusting Time Using Text Functions

Since Smartsheet does not allow direct modifications to system column time values, the best approach is to extract and manipulate the time as text. Below are formulas to extract key date/time components

Below are the formulas using text functions to get Year, Month, Day, Hour, and Minutes as numbers and to get Time text in 24-hour or 12-hour format.

[Year] =VALUE(20 + MID(Created@row, 7, 2))
[Month] =VALUE(LEFT(Created@row, 2))
[Day] =VALUE(MID(Created@row, 4, 2))
[Time 24] =TIME(RIGHT(Created@row, 8), 1)
[Hour] =VALUE(LEFT([Time 24]@row, 2))
[Minute] =VALUE(RIGHT([Time 24]@row, 2))

[Time Text 24] =RIGHT("0" + Hour@row, 2) + ":" + RIGHT("0" + Minute@row, 2)
[Time Text 12] =RIGHT("0" + IF(Hour@row > 12, Hour@row - 12, Hour@row), 2) + ":" + RIGHT("0" + Minute@row, 2) + IF(Hour@row > 12, " PM", " AM")

Note that using the TIME function to convert to 24-hour format makes the formula easy.