Health Status

Jackie H.

Hi!

The sheet that I am developing has items with a due date far in advance. I would like to have my "Health" column show a Grey symbol for items that have not yet started that are more than 60 days out.

=IF(AND([% Complete]@row < 0.25, [End Date]@row < TODAY(15)), "Red", IF(AND([% Complete]@row < 0.75, [End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [End Date]@row >= TODAY(15)), "Green", IF(OR([Start Date]@row <= TODAY(60)), [% Complete]@row = 0, "Gray"))))

KPH

Hi

The correct syntax for the final IF statement is

IF(OR([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Gray")

instead of

IF(OR([Start Date]@row <= TODAY(60)), [% Complete]@row = 0, "Gray")

So your full formula is:

=IF(AND([% Complete]@row < 0.25, [End Date]@row < TODAY(15)), "Red", IF(AND([% Complete]@row < 0.75, [End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [End Date]@row >= TODAY(15)), "Green", IF(OR([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Gray"))))

However, I'm not sure if you meant OR or AND. It sounds like you want gray if the start date is more than 60 days out AND the project is 0% complete.

In which case your full formula would be

=IF(AND([% Complete]@row < 0.25, [End Date]@row < TODAY(15)), "Red", IF(AND([% Complete]@row < 0.75, [End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [End Date]@row >= TODAY(15)), "Green", IF(AND([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Gray"))))