Calculating average time spent in each phase

Kirsten Merrill

I am looking to create a sheet that moves through our 5 stages of work and how much time is spent in each phase. What is the best way to go about this?

Paul Newcome

The most straight forward would be to set up a date column for when you start each phase and then when you end the last phase (or when you start the first phase and then when you end each phase). From there it is subtracting one date form the other to determine how much time was spent in each.

Slightly more complex would be using a dropdown for the different phases and then using Record A Date automations to automatically populate the date columns when the status changes.

Both are relatively straight forward. It just depends on your structure and personal preference.

Paul Newcome

The most straight forward would be to set up a date column for when you start each phase and then when you end the last phase (or when you start the first phase and then when you end each phase). From there it is subtracting one date form the other to determine how much time was spent in each.

Slightly more complex would be using a dropdown for the different phases and then using Record A Date automations to automatically populate the date columns when the status changes.

Both are relatively straight forward. It just depends on your structure and personal preference.

Kirsten Merrill

For the second option, would I still be able to see how long was spent in each phase? I'm assuming that when you use the dropdown to change it to the next phase, the previous phase's date would no longer be visible, meaning I would not be able to pull that past data. Does that make sense?

Paul Newcome

You would have separate date columns for each phase.

Phase.....Phase 1 Date.....Phase 2 Date.....Phase 3 Date

Kirsten Merrill

https://community.smartsheet.com/discussion/comment/318136#Comment_318136

Thanks, this was really helpful! So, now that I know to calculate how long was spent in each stage, I just can't figure out how to set it up so that it grabs those calculations on its own. For example: let's say tomorrow another stage is completed. I don't want to have to go in the sheet every time a stage is completed to calculate how long was spent in that stage, and the NETWORKDAYS function won't work unless it's grabbing from two dates, meaning I can't set it up ahead of time.

What is the workaround for this?

Paul Newcome

Were you wanting the calculations in separate columns for each phase? If so, you can go ahead and use the NETWORKDAYS function and wrap it in an IFERROR to output a blank if there is no phase completion date.

=IFERROR(NETWORKDAYS(............), "")

Becky Wilson

Hi Paul,

Is there a way to get an average of number of networkdays for each phase. For example, I have over 300 rows and I want to average the number of days between each review phase. I don't need to know average per row, just overall average per phase.

Paul Newcome

@Becky Wilson You would have to first calculate it on a row by row basis. You could then get the average from the helper column.

Becky Wilson

Thanks. Was hoping to avoid that