IF OR AND Statement
I am trying to return color symbols for in a sheet.
End goal
Date Null, Completed Null, or Past DATE(2020, 3, 31) = "Red"
Date is Not "Yes" and Not Past DATE(2020, 3, 31) = "Yellow"
Else = "Green"
Here is the code
=IF([Date Completed]1 > 0,IF(AND(NOT(Completed1 = "Yes"),[Date Completed]1 > DATE(2020, 3, 31)),"Red",IF(AND(NOT(Completed1 = "Yes"),[Date Completed]1 <= DATE(2020, 3, 21)), "Yellow","Green")), "Red")
Answers
-
Hi David,
It looks like you're just missing the OR statement at the beginning for your first instruction where the Date Completed is "null". I've removed out the "Red" instruction at the end of your formula, as well, since that will be included in the OR statement.
Try This:
=IF(OR(ISBLANK([Date Completed]@row), AND(NOT(Completed@row = "Yes"), [Date Completed]@row > DATE(2020, 3, 31))), "Red", IF(AND(NOT(Completed@row = "Yes"), [Date Completed]@row <= DATE(2020, 3, 21)), "Yellow", "Green"))
I've also adjusted your 0 to be ISBLANK instead, unless you actually are entering 0's into your Date Completed column. You'll notice I prefer to use the @row function instead of referencing the row number, as well.
Here are some Help Articles I used to write this - let me know if you have any questions: OR Function / ISBLANK Function
Cheers!
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!