How do I show health based on end date & % complete?

Michelle Vidal

I'm trying to show health of a project based on how far out from End date + how much of the work has been completed.

Green:

100% complete before or on End date

Yellow

70-99% and today is < 1 range of End Date

<69% and date within 2 days of End Date

Red

<69% and today is <1 range of End Date

This was what I wrote, and I keep getting "Unparseable"

=IF([%Complete]@row=1, "Green", IF((OR(AND[% Complete]@row<1, [%Complete]@row>.69, TODAY(-1)<[End Date]@row), (AND[%Complete]@row <.7, TODAY(-2)<[End Date]@row) ,"Yellow", "Red")

Werner Gerstacker

Not 100% sure about the logic, but try this:

=IF([% Complete]@row = 1, "Green", IF(OR(AND([% Complete]@row < 1, [% Complete]@row > 0.69, TODAY(-1) < [End Date]@row), AND([% Complete]@row < 0.7, TODAY(-2) > [End Date]@row)), "Yellow", "Red"))

Please note that you had both versions [% Complete] as well as [%Complete], please pick the one that's correct and replace it in my formula if necessary.

Werner Gerstacker

Not 100% sure about the logic, but try this:

=IF([% Complete]@row = 1, "Green", IF(OR(AND([% Complete]@row < 1, [% Complete]@row > 0.69, TODAY(-1) < [End Date]@row), AND([% Complete]@row < 0.7, TODAY(-2) > [End Date]@row)), "Yellow", "Red"))

Please note that you had both versions [% Complete] as well as [%Complete], please pick the one that's correct and replace it in my formula if necessary.

Michelle Vidal

Thank you!!! I was so close! I also added a part for "blank" if not started and within a range of the Start Date, so now the formula reads as:

=IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY(1), (ISBLANK([% Complete]@row))), "", IF([% Complete]@row = 1, "Green", IF(OR(AND([% Complete]@row < 1, [% Complete]@row > 0.69, TODAY(-1) < [End Date]@row), AND([% Complete]@row < 0.7, TODAY(-2) > [End Date]@row)), "Yellow", "Red")))