Formula for tracking the Health of In Progress project timelines

Question

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

Lucas Rayala · Accepted 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")))
)