Project Status Count

Kelly Sessums

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!

SmartLew

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