How to use Nested IF AND/OR Formulas?

Lekha Oberoi

I want to compare two columns Health and Date and populate the column for update required or not required.

Conditions are

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

The formulas I have is

1. =IF([Target date]35 > TODAY(), "NA", IF(OR(Health35 = "Yellow", Health35 = "green", Health35 = "Red"), "NA", IF(AND([Target date]35 < TODAY(), Health35 = "Blue"), "NA", "Update Required")))
2. =IF(AND(TODAY() > [Target date]34, (Health34 = "Blue")), "NA", "Update required")

The first one will not work if the date is in the future, second one does not work if the date is in the past and the health is anything other than blue. Kindly Help!

Paul Newcome

Try this one...

=IF(Health@row = "Blue", "Update Not Required", IF([Target Date]@row> TODAY(), "Update Not Required", "Update Required"))

Paul Newcome

Try this one...

=IF(Health@row = "Blue", "Update Not Required", IF([Target Date]@row> TODAY(), "Update Not Required", "Update Required"))

Lekha Oberoi

Thanks Paul! It did solve the problem.

Paul Newcome

Happy to help. 👍️