Time function Glitch

Options
EYGE
EYGE
edited 05/31/24 in Formulas and Functions

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/01/24
    Options

    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.

    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.

  • EYGE
    Options

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!