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

Options
SLaney
SLaney ✭✭
edited 02/15/24 in Formulas and Functions

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

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Options

    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

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • Eenmeier
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!