Calculating date/time on "Created" and "Modified" column types due to API time zone issue
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?
Answers
-
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:
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.
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!