IF AND OR Formula *almost* works correctly but one part!

Jess S PDX

Hiya folks- all arguments work correctly except turning Health the color Yellow when Recommended Due Date is within 3 days of current date and status is not Complete.

Here is what I need to accomplish:

IF [Status] is"Complete" or "Cancelled/NA" then [Health] ="Blue"

IF [Recommended DueDate] <= TODAY and [status] <> "complete" then [health] ="red"

IF [Recommended DueDate] <= TODAY(-3) and [status] <> "complete" then [health]= "yellow"

IF none of the aboveconditions exist [Health] = "green"

Current and almost accurate formula:

=IF(OR(Status@row = "Complete", Status@row = "Cancelled/NA"), "Blue", IF(AND([Recommended Due Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Recommended Due Date]@row <= TODAY(-3), Status@row <> "Complete"), "Yellow", "Green")))

Note: I did try changing the last "AND" to an "OR" and it turned it yellow BUT it also turned other things yellow that it should not have.

Paul Newcome

First thing to note is that since you already have the "Complete" argument taken care of in the first IF, you don't need to specify anything about that in the following IFs.

The issue with your yellow is a misstatement. What you want and what you are typing are two different things.

"Recommended Due Date is within 3 days of current date" would be less than or equal to 3 days in the future, but your formula is looking for dates that are more than 3 days past which is already covered by the Red argument. Try changing the -3 to 3 and see if that works for you.