Question on Function Syntax using IF, AND, and OR
I am trying to get an At Risk check box to return a red flag if there is a date in End Date and that date is earlier than the curent date and the Status cell shows one of three statuses. Can't quite get the formula logic. Any suggestions would be wonderful. Thanks!
Desired results:
End Date is blank = no flag
End Date is not blank and:
End Date is greater than current date = no flag
End Date is less than current date and:
Status is any of "Complete", "Executed", or "Recorded" = flag
Current attempt:
=IF(AND(NOT(ISBLANK(End Date@row))), (AND(End Date@row < TODAY()), (OR(NOT(Status@row = "Complete")), (NOT(Status@row = "Executed")), (NOT(Status@row = "Recorded")))), 1, 0)
Best Answer
-
Try this:
=IF(AND([End Date]@row <> "", [End Date]@row < TODAY(), Status@row <> "Complete", Status@row <> "Executed", Status@row <> "Recorded"), 1)
Answers
-
Try this:
=IF(AND([End Date]@row <> "", [End Date]@row < TODAY(), Status@row <> "Complete", Status@row <> "Executed", Status@row <> "Recorded"), 1)
-
Hi @MLK ,
See if this works for you.
=IF(AND([End Date]@row <> "", [End Date]@row < TODAY(), OR(Statuts@row = "Complete", Statuts@row = "Executed", Statuts@row = "Recorded")), 1)
Hope this helps,
Dave
-
@Paul Newcome That worked perfectly, and taught me a lot about how the formulas work. Thanks!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!