Phantom time zone switching
I recently created a helper sheet for the purpose of generating a multirow report from a much larger intake sheet.
My helper report sheet is calculating the max entries on the main sheet, grabbing the last 50 rows, and looking for criteria matches that I have setup in helper columns in the report sheet. Its mostly working as I intended but I have some issues with time zones.
I sent out a reminder for all users who use the main sheet to update their time zone to EST & I haven't observed any time changes in the log of the main sheet in over a week now.
What I'm observing now is on the helper sheet the times will sometimes be PST as soon as I open the sheet and quickly update to EST. I can repeat this behavior as long as I don't save the sheet, so reload the page = 1 second of PST then back to EST. The result of this jankiness is I'm losing rows in my match criteria. So if you run the report automation without accessing the sheet first its dropping rows.
Checking the logs of the sheet shows I'm the only person accessing the sheet. So, since I'm set to EST you would think it would always be EST but apparently not.
Any suggestions?
Answers
-
Welcome to the world of Smartsheet time handling. This goes into all the detail about it.
Time zones and sheets
When using the TODAY() formula, Smartsheet calculates it based on the time zone of the person viewing the sheet.
When working with automations, the trigger’s time zone is the same as the sheet Owner’s time zone.
Time zones and system columns
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.
Pacific Time overrides
When changes are made to a sheet via automation, cell links, or cross-sheet formulas, the whole sheet is converted to Pacific Time. Automation services are hosted by service users with a Pacific Time setting. This means that TODAY() formulas and display system column display values may get updated if you're in a different time zone.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
The sad thing is I've read that article and it still isn't clear to me what exactly is going on. I'm sure how Smartsheet handles time zones makes perfect sense to their development team and Smartsheet Pros but for the rest of us?
I would prefer to not have to tailor my formulas around weird (to me) backend changes that are going on. It would make more sense to me that due to the fact that my entire team is EST that I can flag our sheets/automation as EST and from that point on anything going on behind the scenes is transparent to the end user.
I think what I'm going to end up doing is stop relying on exact time measurements because I can't think of a way to do what I want to do elegantly.
-
to help out. Automations/work flows are on what ever time zone the created of the workflow is. Today() function is basically a system function so it’s on gmt. Formulas are on pst UNTIL the sheet is opened. Which is why you see it change. There are work around. Like using a workflow to fill a date to bypass the today function. And some different ones for time. You can also use work flows to force the sheet to update even when not actively viewing the sheet. Most of them are fairly simple. There are so many ways that posting them all isn’t really the best. @Paul Newcome or @Genevieve P. could probably give you the best work around to achieve what ever your wanting to do.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Hi @Mark.poole
I get this issue as well, though it never seems to self-rectify. I'm based in Adelaide, South Australia.
The source cell is always correct, but random cells in the target sheet revert to Pacific Time occasionally.
One fix I've found is to open the source sheet, click on any cell (full, empty, blank row, full row, doesn't matter) and hit 'Save'.
Back to target sheet and the time updates.
Cheers
Update - it's just changed a Target Cell to Pacific Format, when the last update (which triggers the change) was on April 28th. Now that bit makes even less sense :D
