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.

Find more posts tagged with

Time Calculation

timekeeping

timestamp

Accepted answers

All comments

Mark.poole

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.

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones#:~:text=in%20Personal%20Settings.-,Time%20zones%20and%20sheets,the%20sheet%20Owner's%20time%20zone.

EYGE

@mark.poole - Ok thank you for sharing the information about time zones good to know, I'll have to find a work around.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.