Calculate cumulative duration of time spent in each status where status changes are bidirectional

07/22/21
Answered - Pending Review

Hi,

I have a problem I am trying to solve in a sheet that contains hundreds of individual requests. Each request starts with Status = 'New' and finishes at Status = "Complete" in between there are 3 review statuses, "Review Department A", "Review Department B", "Review Department C" depending on the department the review is with. These review statuses move back and forth until all issues have been resolved with the request

How would I calculate the total duration in days of the time spent in each status?

A status history for one request on this sheet looks like this

I cant use the dates applied in the Cell History with formulas/automation, and I don't think the record date automation will help as this will overwrite the previous date recorded for each status.

Thanks,

Ross

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    edited 07/22/21

    Hi @Ross Rowntree 

    Hope you are fine, please try the following:

    1- create 4 helper column ( Dept A - Dept B - Dept C - Complete ) Date type columns

    2- create 4 workflow automation to record the date as following:

    A- When task change to Dept A record the date in Dept A helper column.

    B- When task change to Dept B record the date in Dept B helper column.

    C- When task change to Dept C record the date in Dept C helper column.

    D- When task change to Complete record the date in Complete helper column.

    then you can use those helper column to calculate the duration used for each step for example:

    in Dept A = Dept B recorded date - Dept A recorded date

    in Dept B = Dept C recorded date - Dept B recorded date

    in Dept C = Complete recorded date - Dept C recorded date

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • HI @Bassam Khalil

    Thanks for your help! - the major obstacle here is that the statuses can go between Departments i.e. Department C back to Department A or Department B as per the example I attached above. In your helper columns, the original dates populated will get overwritten every time status is reused.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Ross Rowntree

    I hope you're well and safe!

    As Bassam's example, you could structure multiple workflows with even more helper columns for multiple changes.

    Another option could be my method described below but modified to look at the total instead.

    More info: 

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi @Andrée Starå

    Thanks for the suggested solution. That may help but can you offer any more details as to how the solution is implemented?

    Thanks,

    Ross

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 08/05/21

    @Ross Rowntree

    You're more than welcome!

    I'd be happy to share an example. 

    Please send me an email at [email protected], and I'll share it with you.

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.