Tracking Time in Status Levels (New, Review, Hold, Process, Resolved)
I have a maintenance request sheet where user fill out a Smartsheet form and submit their requests. Internally, the request is received as "New" and will be manually changed to various status levels over time (i.e. "New" to "Review" to "Hold" to "Process" back to "Hold" back to "Process" to "Resolved").
My question: how can I track the time in days of each status per request? Specifically, in the example above, the request was on "Hold" twice, 10 days the first time + 8 days the second time and then in "Process" 2 days and then 3 days. Is there a way to track that this request was "New" for 1 day, "Review" for 3 days, "Hold" for 18 days total, "Process" 5 days total, and then "Resolved" for however many days it has been since it was "Resolved"?
I can manually track the cell history changes and figure this out, but I would prefer an automated way to track this. Perhaps, I could set up an automation that timestamps a separate sheet each time the status is changed? Thoughts?
Thank you!
Starbuck Laney, PMP
Project Manager | Engineering Consultant | Lean Six Sigma Green Belt | Cybersecurity
Starbuck Laney, PMP | LinkedIn
Answers
-
Hello @SLaney,
For the statuses that will only be selected once, you can created a hidden buddy column for each and create an automation to Record a Date in the corresponding buddy column. For example, create a hidden Review Date column and have an automation record the date in this column when the status changes to Review. Then, you can have a formula auto-calculate Review Date minus Created Date to see how long it sat in New status.
It gets a little more complicated when you have a status being re-visited such as your Hold and Process statuses. You could create multiple buddy columns to mark how many times a row has gone into hold/process status and record a date for each, but there's likely an easier way that others might be able to enlighten us on.
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
Hello. Following this thread. The ability to track the number of days a project is placed on hold is a pretty important and common project management need. I am hoping Smartsheets has a way to do this already in the platform easily.
-
Starbuck, did you find a solution for your needs? I have the same question an business need, but the proposed solutions to date don't seem to fit our needs.
-
I have the same question. we use our sheet to log intake requests and how long they are waiting on each status' to track where the lag time is.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!