Project Status Count
Hi. I am creating a project dashboard and would like to know if there is a way to include a count of weeks a project status has been at different levels. I have a sheet for each project and an overall selection for the project status on the sheet. I would like to keep track of how many weeks a project was in each status during the lifecycle of the project. For example, a 2 month project, which is now complete, was in green phase for 6 weeks, and yellow phase for 2 weeks, I would like to dashboard to show this historical information for quarterly metrics.
I realize this requires smartsheet to run a status check weekly and build on the count. I am fairly new to this tool and am not even sure if this is a capability.
Any help or guidance would be appreciated.
Thanks!
Answers
-
Hi Kelly,
If you are new to the system a quick way without too many formulas may be to have come columns which auto populate a date based on the status changing during the life cycle.
So for example you could have an automation that filled a date in the green phase column when the status changed, and then in yellow phase and so on and so forth.
Utilise this automation with conditions :
to get something like this:
This would allow you to calculate days between each date. You could create summary fields with this formula. (Change the 1 for whatever row number you are using)
For days in the green phase , =NETDAYS([Project Start]1, [Green Phase]1)
Days in Yellow Phase , =NETDAYS([Green Phase]1, [Yellow Phase]1)
For weeks, just divide by 7, i.e =NETDAYS([Project Start]1, [Green Phase]1) / 7
You can then reference these sheet summary numbers at widgets on a dashboard
Let me know if that helps
I'm passionate about helping you leverage the truly awesome power of smartsheet!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!