Formula for tracking the Health of In Progress project timelines

Hello,

I would like to build off of this formula:

=IF(AND([Target End Date]@row > TODAY(+10), [% Complete]@row < 1), "Green", IF(AND([Target End Date]@row < TODAY(+5), [% Complete]@row < 1), "Yellow", IF(AND([Target End Date]@row <= TODAY(), [% Complete]@row < 1), "Red")))

I would like for any project not "In process' to show up as "gray'.

Any suggestions will be appreciated!

Kate

Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Hi @Kate Condon, the easiest thing to do is to add your new requirement at the beginning, and wrap everything else. Assuming the a project that is not In Process has a % Complete of zero or blank, you could use the below:

    =IF(OR([% Complete]@row=0,[% Complete]@row=""), "Grey", 
    IF(AND([Target End Date]@row > TODAY(+10), [% Complete]@row < 1), "Green", IF(AND([Target End Date]@row < TODAY(+5), [% Complete]@row < 1), "Yellow", IF(AND([Target End Date]@row <= TODAY(), [% Complete]@row < 1), "Red")))
    )
    

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Hi @Kate Condon, the easiest thing to do is to add your new requirement at the beginning, and wrap everything else. Assuming the a project that is not In Process has a % Complete of zero or blank, you could use the below:

    =IF(OR([% Complete]@row=0,[% Complete]@row=""), "Grey", 
    IF(AND([Target End Date]@row > TODAY(+10), [% Complete]@row < 1), "Green", IF(AND([Target End Date]@row < TODAY(+5), [% Complete]@row < 1), "Yellow", IF(AND([Target End Date]@row <= TODAY(), [% Complete]@row < 1), "Red")))
    )
    
  • Kate Condon
    Kate Condon ✭✭✭

    This worked! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!