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

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 Khalil
    Bassam 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

    bassam.khalil2009@gmail.com

    ☑️ 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 EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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 andree@workbold.com, 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 EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!