Help with formulas to drive Harvey Ball Status

Carrie Marks Burroughs

Hello!

I am trying to automate the Harvey Ball (R, A, G, Gray) status indicator on my project plan sheet. I am very new to formulas and am not sure what I am doing wrong.

Below is a screenshot of the implicated Columns:

The values in Status are: Not Started, In Progress, Complete. Harvey Balls are in Health: Green, Yellow, Red, Gray.

This is what I would like to design:

Green is Complete (regardless of End Date value)

Yellow is In Progress AND Today is less than or equal to to End Date

Red is In Progress or Not Started AND Today is greater than or equal to End Date

Gray is Not Started and Today is less than or equal to End Date

This is the formula I have been using in Health:

=IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "Complete", TODAY() > [End Date]12), "Red", IF(AND(Status@row = "Not Started"), "Gray", "Yellow")))

The formula seems to be wrong because sometimes it display Red if Start and End Dates are both empty, and sometimes it displasy Gray if same. It always displays Red when there are no dates in the Start/End Date columns.

Is my mistake obvious? I really appreciate your time!

Paul Newcome

Try this one...

=IF(Status@row = "Complete", "Green", IF([End Date]@row >= TODAY(), IF(Status@row = "In Progress", "Yellow", "Gray"), "Red"))