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

Options

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

• ✭✭✭✭✭✭
edited 07/22/21
Options

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

bassam.khalil2009@gmail.com

• Options

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.

• ✭✭✭✭✭✭
Options

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.

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.

• Options

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

Thanks,

Ross

• ✭✭✭✭✭✭
edited 08/05/21
Options

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!