Calculating average time spent in each phase
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?
Best Answer
-
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.
Answers
-
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.
-
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?
-
You would have separate date columns for each phase.
Phase.....Phase 1 Date.....Phase 2 Date.....Phase 3 Date
-
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?
-
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(............), "")
-
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.
-
@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.
-
Thanks. Was hoping to avoid that
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!