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 ✭✭✭✭✭✭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.
thinkspi.com
Answers
-
Julio S. ModeratorHi@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 thefollowing postfrom@Paul Newcomethat 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
-
HardWork ✭
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过程将列变成24ur 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 ✭✭✭✭✭✭
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.
thinkspi.com
Help Article Resources
Categories
Check out theFormula Handbook template!
If I understand correctly, this may help.<\/p>
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107116,"type":"question","name":"I'd like to create a column formula that references a specific cell with acceptable syntax","excerpt":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…","snippet":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to…","categoryID":322,"dateInserted":"2023-06-29T23:48:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T22:48:54+00:00","insertUserID":163028,"insertUser":{"userID":163028,"name":"jcabaniss","url":"https:\/\/community.smartsheet.com\/profile\/jcabaniss","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-01T02:58:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3376249626,"url":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","format":"Rich","lastPost":{"discussionID":107116,"commentID":383416,"name":"Re: I'd like to create a column formula that references a specific cell with acceptable syntax","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383416#Comment_383416","dateInserted":"2023-06-30T22:48:54+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T15:52:25+00:00","dateAnswered":"2023-06-30T00:15:36+00:00","acceptedAnswers":[{"commentID":383245,"body":"
