Trouble with Time Tracking

We are working on a sheet to show the avg time spent on our tasks and we figured out how to time track as Smartsheet does not yet allow time tracking.

Our solution was when someone selects "in progress" the row will be copied to another sheet. In that sheet we have a formula to grab the time from the created date and translate it to military time. this row is then moved to our time tracking sheet.

When someone selects "done" the row will be copied to a separate sheet and get the time the same way as the in progress sheet. We then use index/match to show that time in the time tracking sheet and subtract the difference to get the minutes spent.

Our issue is that the times are showing 3 hours before the time the box was checked, but only sometimes. We are guessing its due to the time zone but it's causing our times to be skewed as some are correct and some are 3 hours ahead.

Is there a way we can fix this? or is it simply a bug.

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    Because you're extracting time as text, it gets "locked in" to the time zone of the last person who last viewed the sheet and won't adjust back when someone else opens the sheet from the time zone that you expect.

    I don't think there's a way to address this, other than restricting those intermediary copy sheets to one or two people in the same time zone so you have consistency.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!