Health formula from Status

Christine Brown (SAI)

Hi all. I'm new and trying to tweak a health column formula. We've decided to remove our % Complete column which we'd originally used.

Original Formula:

=IF(AND([Due Date]@row < TODAY(), NOT(ISBLANK([Due Date]@row)), OR([% Complete]@row <> 1, ISBLANK([% Complete]@row))), "Red", IF(AND(NOT(ISBLANK([Due Date]@row)), OR([% Complete]@row = 0, ISBLANK([% Complete]@row))), "Gray", IF(AND(([% Complete]@row < 0.5), [Network Days]@row <= 2, NOT(ISBLANK([Due Date]@row))), "Yellow", IF(NOT(ISBLANK([Due Date]@row)), "Green ", " "))))

I'd like to use a simple logic such as:

Past due date and NOT complete = Red

Not started = Gray

In progress = Green

Blocked = Yellow

Can anyone please recommend a formula that would work?

Thank you!

Mark Cronk

Hi @Christine Brown (SAI) ,

I assumed your comlplete was a checkbox and you previous "yellow" formula was the criteria you're using for Blocked. if not, you'll need to adjust. Try:

=IF(OR([start date]@row>=today(), ISBLANK([start date]@row)), "Gray", IF(AND([due date]@row<Today(), complete@row=0), "Red", IF(AND([Network Days]@row <= 2, NOT(ISBLANK([Due Date]@row)), "Yellow", "Green")))

Help?

Mark

Christine Brown (SAI)

Thank you @Mark Cronk ! I'm still getting a syntax error unfortunately. I think we don't want Yellow/Blocked associated with the date, just status change. We currently had a green strikethrough for complete items.

I will continue to tinker. Thanks again!