Nested IF AND/OR Formulas
I am trying to compare Date and Health columns and derive if it requires an update or not.
Conditions are
- If the date is in the past and the Health is Blue = Update Not Required
- If the date is in the past and Health is either Yellow, Green, Red = Update Required
- If the date is in the future and Health is either Yellow, Green, Red = Update Not Required
- If the date is in the future and the Health is Blue = Update Not required
Right now I have the formulas
- =IF(AND(TODAY() > [Target date]43, (Health43 = "Blue")), "NA", "Update required")
- =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!!!
Answers
-
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
Mathieu | Workflow Consultant
info@evolytion.com
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives