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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives