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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!