Duration Counter/Timer

Is there a way in SS, that when using an auto-generated created date/time, that a duration column will count how long since it was submitted. Then have a date & a time column for completion that will stop the timer?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The closest you could automate the tracking without any manual intervention would be on the hour. Basically you would set up a hidden text/number column and 24 Change Cell Value automations (one for each hour). Then you would insert a Modified (date) column. Finally I am fairly certain there is a solution in the linked thread above that uses the Created and Modified date/times as the start and finish for duration.

Answers

  • Julio S.
    Julio S. Moderator

    Hi @HardWork

    It looks like you'd like to designate a System Column as a Project Column (Start or Finish) to perform calculations with the duration column. Note that only Date columns can be used as Project Columns when dependencies are enabled.

    If you are looking to track time in Smartsheet, I would recommend the following post from @Paul Newcome  that can contain really helpful formulas that can assist you in tracking time in your projects.

    If you'd need further advise, please include some screenshots of your solution (remember to remove or hide any confidential information that shouldn't be shared) as well as more precise information on how you'd like to track time.

    I hope this can be of help.

    Cheers! 

    Julio

  • Hi,

    I have a auto-generated [Created (Date)] that gives me both date and time of the request. I am looking for a way to take the time from the [Created (Date)] minus the date to start a time from when the request came in. Then stop when a decision for Approval or Declined has been made.

    I did try @Paul Newcome process to turn the column into a 24 hour column but I can't get that to work either.

    Tried this and I get "Circular Reference" error message.

    =VALUE(LEFT([Closed Duration]@row, FIND(":", [Closed Duration]@row) - 1)) + IF(CONTAINS("p", [Closed Duration]@row), IF(VALUE(LEFT([Closed Duration]@row, FIND(":", [Closed Duration]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Closed Duration]@row, FIND(":", [Closed Duration]@row) - 1)) = 12, -12))

    Tried this and I only get the hour and not the minutes.

    =VALUE(MID([Created (Date)]@row, FIND(" ", [Created (Date)]@row) + 1, FIND(":", [Created (Date)]@row) - (FIND(" ", [Created (Date)]@row) + 1))) + IF(VALUE(MID([Created (Date)]@row, FIND(" ", [Created (Date)]@row) + 1, FIND(":", [Created (Date)]@row) - (FIND(" ", [Created (Date)]@row) + 1))) <> 12, IF(FIND("P", [Created (Date)]@row) > 0, 12), IF(FIND("A", [Created (Date)]@row) > 0, -12))

    Any suggestions?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The closest you could automate the tracking without any manual intervention would be on the hour. Basically you would set up a hidden text/number column and 24 Change Cell Value automations (one for each hour). Then you would insert a Modified (date) column. Finally I am fairly certain there is a solution in the linked thread above that uses the Created and Modified date/times as the start and finish for duration.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!