Collecting Duration a Row is on a Status

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.

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)

All comments

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.

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

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.

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.

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

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

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).

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.

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

After removing IFERROR:

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})

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

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?

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

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.

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.

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.