Time function Glitch
Hi,
I noticed a strange glitch that occurs when using the Time() function.
I'm basically trying to mimic a stop watch by calculating the duration of a start and stop entry utilizing the created date/time stamp. I have setup automation to move the rows that are entered as stop to another sheet.
I extract the time portion using the formula.
=TIME(SUBSTITUTE([Created Date]@row, DATEONLY([Created Date]@row), ""), 1)
Where I run into issue is when a new entry is added, the stop times on the sheet with all the stop entries roll back by 3 hours (see below screen capture). This of course causes the duration calculations to be off. After a few seconds the system recalculates but you need to manually save for the sheet to take the correct values again, this is not feasible as it needs to be automated feeding to a report & dashboard.
Strangely the same function for the start entries do not have this issue. CORRECTION - same issue happens on the start entries as well
Any ideas is greatly appreciated, I'm completely puzzled.
Answers
-
Based on your description of the issue I can safely assume you are in EST. This is due to how Smartsheet handles time when the sheets are not open. There are a few work arounds to force the sheet to update. Though I think that is more for use of the today function then it is for time. The below is courtesy of the Smartsheet Learning Center.
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.
I would refer to this page for all the ins and outs.
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.
-
@mark.poole - Ok thank you for sharing the information about time zones good to know, I'll have to find a work around.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!