Nested IF AND/OR Formulas

Lekha Oberoi

I am trying to compare Date and Health columns and derive if it requires an update or not.

Conditions are

1. If the date is in the past and the Health is Blue = Update Not Required
2. If the date is in the past and Health is either Yellow, Green, Red = Update Required
3. If the date is in the future and Health is either Yellow, Green, Red = Update Not Required
4. If the date is in the future and the Health is Blue = Update Not required

Right now I have the formulas

1. =IF(AND(TODAY() > [Target date]43, (Health43 = "Blue")), "NA", "Update required")
2. =IF([Target date]41 > TODAY(), "NA", IF(OR(Health41 = "Yellow", Health41 = "green", Health41 = "Red"), "NA", IF(AND([Target date]41 < TODAY(), Health41 = "Blue"), "NA", "Update Required")))

The first one does not work if the health is other than blue and the second one, the second formula does not work if the date is in the future. Kindly help!!!

Mathieu PERSICO

Hi Lekha,

Based on what you wrote, here is the formula I found:

=IF(AND([Target date]1 < TODAY(), NOT(Health1 = "Blue")), "Update Required", IF(OR(AND([Target date]1 < TODAY(), Health1 = "Blue"), TODAY() < [Target date]1), "Update Not Required", "NA"))

Hope this helps! Have a nice day,

Mathieu | info@evolytion.com