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.



  • SmartLew
    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

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!