How to calculate resolution time?

Options

I have a sheet, where via form the Project team submits a Change Request. I need to calculate how long it takes the submit the request until it's marked Approved. We are trying to calculate the resolution time

So I have a column called " date of creation" and I have another called "Last review". which will make it easier to calculate. But CR is often modified after it gets approved. So it won't give me a realistic date.

I saw the video https://www.youtube.com/watch?v=tA7Ti6TKkw8 which answer the question: " https://community.smartsheet.com/discussion/70764/populate-a-date-field-when-a-status-is-changed"

Is there no other way? it adds quite a level of workload, taking into account that we have 5-10 CR sheets for all the projects, which then feed into a Dashboard.

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    @Almudena Caro Building one workflow once for each sheet sounds like a clean solution.

    As soon as status changes to Approved, the workflow logs the date in a column. Then your calculation is straightforward.

    (( I'm curious why you have so many change control sheets? Why not run all change control through one sheet? Your form would ask which project needed a CR, then collect all the same information. When a CR is submitted, an alert could go to the proper team or PM based on the project. ... just something to consider. ))

    dm

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!