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.

Jess S PDX

@Paul Newcome You, sir, are a scholar and a gentleman—That did the trick! I'm both humbled and grateful for the help and the tip (AI wrote the formula but noted). Thank you again!

Paul Newcome

Happy to help. 👍️

Jess S PDX

@Paul Newcome Happy Friday. New Roadblock as it relates to the health - Want one field to show Overall Project Health. Ideally, we would figure out how to average the colors and/or tasks past due to return RYBG for the overall projects (about ~230) so that we can report it on a Dashboard. Any ideas on formula would be so appreciated.

Circular reference error formula in OPH row: =IF(Level@row = 0, =AVG(Health:Health), "")

Blocked error Health row formula: =IF(Summary@row = 1, "", IF(Level@row = 0, [Overall Project Health]@row, 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")))))

Grateful for any guidance you or others may have.

Paul Newcome

How exactly would you want the average to be calculated? Would certain colors be weighted such as Red counts as two but Green ounts as one, or some other logic?