IF(And/OR) Formula Help
Hello,
I have the below formula I am working from and everything works EXCEPT the last part of the formula where we are asking for the health to show as blank if the start date is blank or the status is Not started.
Also, we were using If(AND prior to using IF(OR and wondering what the true difference is. If we use "OR" is it saying if the status is "In Progress" OR the End Date is greater than 10 days then it's green. If that is the case I suppose we would want to go back to AND.
=IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(OR(Status@row = "In Progress", [End Date]@row < TODAY(11), [End Date]@row >= TODAY(3)), "Yellow", IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() < 3), "Red", IF(OR(Status@row = "Complete", "Blue", IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() > 29), "Green", IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() < 11), "Red", IF(OR(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(OR(Status@row - "Not Started", [Start Date]@row, "", ""))))))))))
Answers
-
Replace your typo - with = in your last IF to check Status@row = "Not Started" and also your closing bracket for OR is misplaced
Replace IF(OR(Status@row - "Not Started", [Start Date]@row, "", "") to
IF(OR(Status@row = "Not Started", ISBLANK([Start Date]@row)), "")
In fact, after checking you entire formula also noticed that you have incorrect brackets, rather mistyped OR for the Status@row = "Complete" condition.
Try this as the entire formula,
=IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(OR(Status@row = "In Progress", [End Date]@row < TODAY(11), [End Date]@row >= TODAY(3)), "Yellow", IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() < 3), "Red", IF(Status@row = "Complete", "Blue", IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() > 29), "Green", IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() < 11), "Red", IF(OR(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(OR(Status@row = "Not Started", ISBLANK([Start Date]@row)),"" ) ) ) ) ) ) ) )
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!