Formula to count days a row stays in a certain status?

LegalPat

For a contract management sheet, we are using a drop down single-select to choose the contracts status, ie. new request, attorney review, awaiting signature... I want to measure the average time any row stays in a certain status. The idea is that we can track how many days on average attorney review takes, or how many days contracts spend awaiting signature, etc.

Any help is appreciated, thank you.

Andrée Starå

Hi @LegalPat

I hope you're well and safe!

You could use so-called helper columns combined with Workflows and the Record a date Action.

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!

Jeff Reisman

@LegalPat

What you'll need to accomplish this is, to start with, a helper date-type column for each status value. So, a New Request Date column, an Attorney Review Date column, etc.

For each status, you'll need an Automation workflow rule (or one Automation with condition paths for each status value.) Example:

Trigger: When rows are added or changed, when Status column changes to any value

Condition Path 1: When Status = "New Request"

Action: Record a Date in the New Request Date column.

Condition Path 2: When Status = "Attorney Review"

Action: Record a Date in the Attorney Review Date column.

Once your automation is set up and your date values start populating, You'll need to calculate the number of days between each date on each row. So, a Duration column between each date column and its proceeding date column. In each you'll want to use NETWORKDAYS function to find the duration, for instance in "Days From New Request to Review" duration column:

=NETWORKDAYS([New Request Date]@row, [Attorney Review]@row)

You can find your average days a few different ways. You can use summary fields:

Create one for each time frame you want to average. For instance, for New Request to Attorney Review, use a formula like:

=AVG([Days From New Request to Review]:[Days From New Request to Review])

Click on the three little dots on the summary field to set number formatting, such as # of decimal places.

Instead of Summary fields, you could reference these duration column from a metrics sheet and present your averages there.

LegalPat

Thank you @Jeff Reisman and @Andrée Starå

The problem is that these statuses do not necessarily change in a sequential order. Instead the status may bounce back and forth between statuses in any random order. For example, some docs may go from "new request" to "attorney review" and others will go from "new request" to "awaiting signature," and there are 6 different statuses possible so the possibilities multiply.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319296#Comment_319296

In that case, good luck 🤣

But honestly, you would have to come up with some criteria to capture with the Record a date and Change cell value automations. Like when the row is added, set the Initial Status value in a field. When a the status is changed, add the new status value into a 2nd Status field, Record the date in a 2nd Status Date field, check a checkbox field for "2nd Status selected". Next condition path: When a the status is changed, and 2nd Status Selected box is checked, and 3rd status selected box is unchecked, add the new status value into a 3rd Status field, Record the date in a 3rd Status Date field, check a checkbox field for "3rd Status selected"... and so on, until you have a huge mess.

Then you have a bunch of metrics for all of it....

Next option:

Add an AutoNumber column that gives each row a unique identifier. Set up some copy row automations to copy rows to a fresh sheet with the same columns, and in that second sheet, include the system-column for Created date.

Automations:

When a row is added, copy that row to the other sheet.

When a status is changed, copy that row to the other sheet.

Now, on your other sheet, you have multiple rows for each unique identifier, with the status and the date on which that status was set (the Created date field). Then you just have to figure out some way to analyze those rows - earliest created date is the first status, next date is the second status, and so on.