IF, AND, OR for task health based on target finish date and status

Tmaid

I'm trying to build a IF forumual in the health column that will have all the conditions built out below.

I use the conditional formatting as a workaround, the conditional formatting is not the end goal.

Column Names

"Health"

"Planned Finish"

"True % Complete"

"Status"

This is what I've build out so far but I'm not sure if I'm on the right track

=IF(AND(Status@row = "Complete", [True % Complete]@row = "100%", "Blue")), IF(AND(Status@row = "Complete", [True % Complete]@row > "100%", "Red")), IF(Status@row <> 1, IF(TODAY() - [Planned Finish]@row > 0, "Red", IF(TODAY() - [Planned Finish]@row > -14, "Yellow", IF(TODAY() - [Planned Finish]@row < 14 = "Green")))), IF(AND([True % Complete]@row = "", [Planned Finish]@row > 0, "Red"))

Paul Newcome

Try this:

=IF(Status@row = "Complete", IF([True % Complete]@row = 1, "Green", "Red"), IF([Planned Finish]@row< TODAY(), "Red", IF([Planned Finish]@row<= TODAY(14), "Yellow", "Green")))

Tmaid

Thank you that worked for part of the condistions. Let me see if I can finished build out the ones for status based on your outline above. ;-)

Paul Newcome

Which ones aren't working or are missing?

Here's a breakdown of the formula:

=IF(Status@row = "Complete", IF([True % Complete]@row = 1, "Blue", "Red"), IF([Planned Finish]@row< TODAY(), "Red", IF([Planned Finish]@row<= TODAY(14), "Yellow", "Green")))

If the Status is Complete --> If the [True % Complete] is 100% then Green. Otherwise Red. This takes care of the first two (although I see now it was supposed to be blue and not green which has been corrected in the above).

If the Status is not Complete and the [Planned Finish] is in the past then Red. This takes care of the last four.

Then we have Yellow for if the [Planned Finish] is in the next 14 days.

And Green for everything else which should just be when the [Planned Finish] is more than 14 days in the future.