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

Find more posts tagged with

Sheets

Formulas

functionality

Automations

IT & Operations

Accepted answers

All comments

Bassam Khalil

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

Ross Rowntree

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å

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:

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

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!

Ross Rowntree

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å

@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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.