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

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

    https://www.fiverr.com/smartlew

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!