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
-
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
☑️ 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"
-
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.
-
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.
-
Thanks for the suggested solution. That may help but can you offer any more details as to how the solution is implemented?
Thanks,
Ross
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!