Collecting Duration a Row is on a Status

Dakota Haeffner_N

Hello,

I am working on a sheet where we have several statuses that can go in just about any order. We have a suggested flow, but it's more to help the user read through and make sure they didn't miss anything. I've listed the statuses below for reference. For example, the user could go from 1 to 2 to 3 to 4c to 14 to 16. I'm trying to collect the duration each row spends on a specific status to then collect Avg, Max, and Mins so we can hold our teams to a standard. I orginally was just using NETWORKDAYS to collect the duration between automated dates triggered by the statuses but the obvious flaw here is that only works if we are going in the exact order. This almost never happens so some data leaks out.

I've seen a suggestion about creating a helper sheet to copy the row each time a status changes, but I'm looking for guidance on how to then collect the durations for each status. Would it be like created date against last modified date or something? That just seems a little shakey.

I appreciate any collaboration.

1 - Not started
2 - Awaiting Requirements
3 - Regulatory Assessment (Ongoing)
4a - Requested from Chamber of Commerce (Ongoing)
4b - Requested from Lab (Ongoing)
4c - Requested from Manufacturing Plant (Ongoing)
4d - Requested from Product Chemist (Ongoing)
4e - Requested from Quality (Ongoing)
4f – Requested from R&D (Ongoing)
4g - Sent to SDS (Ongoing)
4h – Document in preparation by Reg Team (Ongoing)
5 - Regulatory Review and Approval (Ongoing)
6- Sent for Final Approval (Ongoing)
7 – Back and Forth (Ongoing)
8a - Requested from Chamber of Commerce (Ongoing)
8b - Requested from Lab (Ongoing)
8c - Requested from Manufacturing Plant (Ongoing)
8d - Requested from Product Chemist (Ongoing)
8e - Requested from Quality (Ongoing)
8f – Requested from R&D (Ongoing)
8g - Sent to SDS (Ongoing)
8h – Document in Preparation by Reg Team (Ongoing)
8i – Document send to Apostille/Legalization/Notarization (Ongoing)
9 - Regulatory Review and Approval (Ongoing)
10 - Sent for Final Approval (Ongoing)
11 – Back and Forth (Ongoing)
12a - Requested from Chamber of Commerce (Ongoing)
12b - Requested from Lab (Ongoing)
12c - Requested from Manufacturing Plant (Ongoing)
12d - Requested from Product Chemist (Ongoing)
12e - Requested from Quality (Ongoing)
12f – Requested from R&D (Ongoing)
12g - Sent to SDS (Ongoing)
12h – Document in Preparation by Reg Team (Ongoing)
12i – Document send to Apostille/Legalization/Notarization (Ongoing)
13 - Regulatory Review and Approval (Ongoing)
14 - Sent for Final Approval (Ongoing)
15 - Back and Forth
16 – Complete
0 - On Hold

Brian_Richardson

The suggestion to use a Row Copy is a good one. Otherwise you would have to write a formula for every possible combination…essentially impossible.

So, setup an automation that copies the row to a second "status copy" sheet when the status changes. Note that when the copy happens, all columns from your project summary sheet will be copied to the "status copy" sheet. That's ok, you can just ignore them or hide them on the "status copy" sheet if it bugs you.

Now in your "status copy" sheet, setup the following columns:

• Created: add this system column. This will be the "Status Start date" - anytime status changes on your project sheet a row will get copied to this sheet and Created will show the date and time of the copy / status change.
• Status End: enter the following formula and then make it a Column Formula. This formula collects Created dates for all rows after the current row, that match the Project Name and do not match the Status, then picks the first row from that collection as the date. Or, to put another way, this formula looks for the date that the status changed and returns it as the End date for this particular status row.

=IFERROR(INDEX(COLLECT(Created:Created, [Project Name]:[Project Name], [Project Name]@row, Status:Status, <>Status@row, Created:Created, >Created@row), 1), "")

• Duration: enter the following formula and then make it a Column Formula. This takes the difference between the date the Status was copied over, and the next date when it was changed. If there is no next date (meaning…it's the current status), then it takes the difference between the date the Status was copied over and Today.

=IFERROR(NETDAYS(Created@row, [Status End]@row), NETDAYS(Created@row, TODAY()))

Now back to your project sheet.

In the Project Sheet I assume you have a column for duration of each status, you'll need 1 Duration, 2 Duration, 16 Duration, etc.

In each column enter the following formula and make it a Column Formula. Note that the {} references are done by clicking "Reference Another Sheet" when entering the formula for the first time, then find the "status copy" sheet, then select the applicable column and name the reference for that column. You only have to do the reference once, then subsequent formulas you can just copy/paste or type in the {} names without clicking "Reference Another Sheet".

=INDEX(COLLECT({Duration}, {Project Name}, [Project Name]@row, {Status}, "1 - Not Started"), 1)

This formula looks a the "status copy" sheet to match the project name, where status is "1 - Not Started", collects all the matching rows, and picks the first one, then returns the Duration column from that row.

Repeat this formula for each "status duration" column, changing the status text for each column.

Brian_Richardson

Oh, if you don't care to have the status durations per project, but instead just want to get the average duration for all projects for their 1-Not Started value, then that's simpler.

=AVERAGEIF({Status}, "1 - Not Started", {Duration})

Some of the other functions will require COLLECT. For example, there's a SUMIF, and a COUNTIF, but not a MAXIF or MINIF (if you wanted, say, the longest duration or shortest duration). So for those you need to COLLECT the values first like this:

=MAX(COLLECT({Duration}, {Status}, "1 - Not Started")

You'll notice that the format flips between AVERAGEIF and COLLECT. In AVERAGEIF, SUMIF, and COUNTIF, it's (range, criteria, average range) but in COLLECT it's (collect range, criteria range 1, criteria 1)

KPH

Hi @Dakota Haeffner_N

Do you use the system generated created date in your main sheet?

If not, then when the row is copied to the helper sheet each time the status changes the created date will be the date/time it was copied (not the date/time it was originally created). This is pretty reliable (so long as no-one adds the system generated created date column to the main sheet).

If you are using created date in the main sheet, then the created date in the helper sheet will be the original creation date/time for the row and not the date/time it was copied. In that situation, you would need to use modified date. Which is risky if the row is edited. You would need to lock and limit access to the helper sheet.

Also - Be aware that if you use the system generated date/times and are not in UTC time zone you may need to add some workarounds to avoid the issues associated with the time being stored in one format and displayed in another.

How precise do you need your duration to be?

If you are counting minutes/hours then the system generated date/time columns are the way to go. However, if you are tracking days, then you could use an automation to record a date each time a status is changed. That would eliminate the risks described above.

Dakota Haeffner_N

@KPH

So I'm have the system generated dates, but I agree it would be wise to just collect the date with an automation on the helper sheet. I guess I'm looking for advice on how to extract the duration once it is is the helper sheet? How can I write a formula to differentiate between the multiple rows with the same primary column to extract the duration?

Brian_Richardson

The suggestion to use a Row Copy is a good one. Otherwise you would have to write a formula for every possible combination…essentially impossible.

So, setup an automation that copies the row to a second "status copy" sheet when the status changes. Note that when the copy happens, all columns from your project summary sheet will be copied to the "status copy" sheet. That's ok, you can just ignore them or hide them on the "status copy" sheet if it bugs you.

Now in your "status copy" sheet, setup the following columns:

• Created: add this system column. This will be the "Status Start date" - anytime status changes on your project sheet a row will get copied to this sheet and Created will show the date and time of the copy / status change.
• Status End: enter the following formula and then make it a Column Formula. This formula collects Created dates for all rows after the current row, that match the Project Name and do not match the Status, then picks the first row from that collection as the date. Or, to put another way, this formula looks for the date that the status changed and returns it as the End date for this particular status row.

=IFERROR(INDEX(COLLECT(Created:Created, [Project Name]:[Project Name], [Project Name]@row, Status:Status, <>Status@row, Created:Created, >Created@row), 1), "")

• Duration: enter the following formula and then make it a Column Formula. This takes the difference between the date the Status was copied over, and the next date when it was changed. If there is no next date (meaning…it's the current status), then it takes the difference between the date the Status was copied over and Today.

=IFERROR(NETDAYS(Created@row, [Status End]@row), NETDAYS(Created@row, TODAY()))

Now back to your project sheet.

In the Project Sheet I assume you have a column for duration of each status, you'll need 1 Duration, 2 Duration, 16 Duration, etc.

In each column enter the following formula and make it a Column Formula. Note that the {} references are done by clicking "Reference Another Sheet" when entering the formula for the first time, then find the "status copy" sheet, then select the applicable column and name the reference for that column. You only have to do the reference once, then subsequent formulas you can just copy/paste or type in the {} names without clicking "Reference Another Sheet".

=INDEX(COLLECT({Duration}, {Project Name}, [Project Name]@row, {Status}, "1 - Not Started"), 1)

This formula looks a the "status copy" sheet to match the project name, where status is "1 - Not Started", collects all the matching rows, and picks the first one, then returns the Duration column from that row.

Repeat this formula for each "status duration" column, changing the status text for each column.

Brian_Richardson

Also, KPH is right, if you have Created in the first sheet then you cannot use Created in the second sheet. You can solve this though by simply adding a “Start Date” column to your second sheet and an automation that triggers on row add to capture the date into that column. Then adjust the formulas above to use Start Date instead of Created.

KPH

Hi @Dakota Haeffner_N

Once you have a sheet with all your rows and the date the row status changed you can add another column and use a formula to find the difference between the date on the current row and the date on the next row for the same task. This would give you the time spent in the status on that row. From there you can look at min, max, average, etc. Does that sound like it would do what you need?

If so, is your primary column going to be the same throughout the project and unique to the task? You can use that to ensure you are finding the correct next row. If this is the case, I can mock up a sheet and share the formula. If the primary column could be changed during the project, or is not unique, do you have another column that will remain the same and is unique (this could be a locked and hidden auto number column).

Dakota Haeffner_N

@Brian_Richardson

First off, 10/10 response, super easy to follow! I appreciate you!

Second, on the final forumla,

=INDEX(COLLECT({Duration}, {Project Name}, [Project Name]@row, {Status}, "1 - Not Started"), 1)

let's say I only care about the overall duration times not necessarily the individual project. My though would be I could remove the Project Name in the Collect function (below) but that is giving me an #INVALID VALUE response.

=INDEX(COLLECT({SDSH-NRT.Duration}, {SDHS-NRT.Status}, [NRT- Statuses]@row), 1)

FYI, I'm using a separate sheet to gather metrics for a dashboard on the original sheet.

Dakota Haeffner_N

@Brian_Richardson

Now that I'm troubleshooting I'm actually getting invalid Value from the Status End as well.

After removing IFERROR:

Brian_Richardson

Oh, if you don't care to have the status durations per project, but instead just want to get the average duration for all projects for their 1-Not Started value, then that's simpler.

=AVERAGEIF({Status}, "1 - Not Started", {Duration})

Some of the other functions will require COLLECT. For example, there's a SUMIF, and a COUNTIF, but not a MAXIF or MINIF (if you wanted, say, the longest duration or shortest duration). So for those you need to COLLECT the values first like this:

=MAX(COLLECT({Duration}, {Status}, "1 - Not Started")

You'll notice that the format flips between AVERAGEIF and COLLECT. In AVERAGEIF, SUMIF, and COUNTIF, it's (range, criteria, average range) but in COLLECT it's (collect range, criteria range 1, criteria 1)

Brian_Richardson

https://community.smartsheet.com/discussion/comment/444338#Comment_444338

Hmm. You'll get INVALID VALUE when the COLLECT cannot find any rows that meet the criteria and then INDEX tries to return the first value from the COLLECT…which is non-existent.

So, does your sheet have rows with different statuses for that product and later Created times?

Dakota Haeffner_N

@Brian_Richardson

I don't think so. Here is a screenshot with the formula at the bottom.

Brian_Richardson

It’s because your Created dates are all exactly the same. The formula is looking for Created dates/times that are later than the current row Created date/time, and it’s not finding any, so there’s no results for INDEX to give you, hence the blank/error.

you are too fast, in other words 😀

For testing try wait a few minutes, then flip status, wait a couple more, etc.

In real life you wouldn’t expect status to change multiple times a minute I assume. If it does then we will need to extract time and take it into account too.

Dakota Haeffner_N

@Brian_Richardson

Ah that totally makes sense! So the problem is that the system generated Created column, titled Created[System] here, is pulling from the sheet it is copying from. So when these rows are made it will always grab the same date/time.

I really only need the Date so I tried creating a automated Created date field that just records the date when it comes into the sheet but that is giving me an #INVALID COLUMN VALUE error now.

Dakota Haeffner_N

@Brian_Richardson

I figured it out, wrong column type… 😅

It's all working now! Thank you 1000 times over. You are truly deserving on the Overachievers badge.

Brian_Richardson

Glad you got it!