Tracking Time in Status Levels (New, Review, Hold, Process, Resolved)

SLaney

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!

Monique Odom Stearn

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.