Help with multiple IF statements including both OR and AND
I'm trying to write a formula that will check the At Risk box if true and leave it unchecked if not true; this formula will be in the At Risk column.
I have 5 separate arguments which if any one of them is true, should check the At Risk box. The five arguments are:
1. If [start date] is blank
2. If [finish date] is blank
3. if [assigned to] is blank
4. if [finish date] is in the past AND status does not equal "complete"
5. if [start date] is in the past AND status = "not started"
Here's the formula I've tried:
=IF([Anticipated Start Date]@row = "", 1, IF([Anticipated Finish Date]@row = "", 1, IF([Assigned To]@row = "", 1, IF(AND([Anticipated Finish Date]@row < TODAY(), Status@row <> "Complete", 1), IF(AND([Anticipated Start Date]@row < TODAY(), Status@row = "Not Started", 1), 0)))))
Any help would be SO appreciated!
Best Answer

It just looks like you closed your AND's in the wrong spot.
Try:
=IF([Anticipated Start Date]@row = "", 1, IF([Anticipated Finish Date]@row = "", 1, IF([Assigned To]@row = "", 1, IF(AND([Anticipated Finish Date]@row < TODAY(), Status@row <> "Complete"), 1, IF(AND([Anticipated Start Date]@row < TODAY(), Status@row = "Not Started"), 1, 0)
Answers

It just looks like you closed your AND's in the wrong spot.
Try:
=IF([Anticipated Start Date]@row = "", 1, IF([Anticipated Finish Date]@row = "", 1, IF([Assigned To]@row = "", 1, IF(AND([Anticipated Finish Date]@row < TODAY(), Status@row <> "Complete"), 1, IF(AND([Anticipated Start Date]@row < TODAY(), Status@row = "Not Started"), 1, 0)

Thank you!!! That worked! You just saved my weekend… 🙌
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!