Heath Calculation based on Critical Path Health

ZachTenHaken
edited 12/09/19 in Smartsheet Basics

Hello!

 

I'm wanting to calculate the health of my project based on the health of any task that is on the critical path. 

 

I have seen the blog post about being able to create a filter that is able to show the critical patch, but is there a way to derive via a formula if the task is on the critical path or not? I would probably use a checkbox column and have a simple formula such as IF[Task]@row = "Critical Path", "True", "False")... Then I could integrate this into my health formulas. 

 

I'm assuming no since I've not been able to find a lick of documentation with this mentioned... Figured I would poll the community. 

 

Thanks in advance!

 

Zach

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Zach,

    Unfortunately, as far as I know, it's not possible at the moment, but it's an excellent idea!

    Please submit an Enhancement Request when you have a moment.

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Zach,

     

    You can use this formula for checking the health of a TASK in the SHEET. You will have to incorporate this with your Critical Path Logic to get what you want. This is just a RAG status for the task. Please Create a NEW Column called "RAG" and enter this formula in CELL RAG1 , then copy drag down.

     

    =IF([Task Name]1 <> "", 

    IF([% Complete]1 = 1, "Complete", 

    IF(AND(Finish1 >= TODAY(), [% Complete]1 = 0), "Not Started", 

    IF(AND(Finish1 < TODAY() + 7, [% Complete]1 < 1, [% Complete]1 >= 0.8), "Yellow", 

    IF(AND(Finish1 < TODAY() + 7, [% Complete]1 < 0.8), "Red", IF(AND(Finish1 > TODAY() + 60, [% Complete]1 > 0), "Green", 

    IF(AND(Finish1 > TODAY(), Start1 > TODAY(), [% Complete]1 > 0), "Green", 

    IF(AND(Finish1 > TODAY(), Start1 > TODAY(), [% Complete]1 = 0), "NotStarted", "Green"))))))), "")

     

     

    Conditional Formatting for RAG.png

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree is correct. There is no way to directly reference the Critical path via a specific reference that could be included in a formula or function.

     

    I reached out to SS about this very thing some time back. The two options you have are to either create some formula that spells out the critical path or manually mark each row that is on it.

     

    That particular project unfortunately got put on the back burner for me, so I haven't been able to work on writing something out to automatically check a box based on critical path criteria yet.