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!
Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>
<\/p>
When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/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":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":57,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/0T1JMLQOPB6K\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
