Help with a formula for: IF field1 = A AND field2 = x or y or z then
It seems like this should be simple but I cannot find any examples, and have not been successful with various attempts. In plain language I want to:
IF Today’s date > Target End Date
AND Status IS NOT EQUAL “Complete” or “Cancelled”
Then “Overdue”
Else nothing
Alternatively I would be happy to change the Status to EQUAL "In Progress" or "Not Started" or "On Hold" or "Blocked" if that is easier.
It is the multiple values for a single field I can't seem to make work with the AND statement. This works:
=IF(AND(TODAY() > [Target End Date]@row, Status@row = "In Progress"), "Overdue") as does this:
=IF(AND(TODAY() > [Target End Date]@row, Status@row <> "Complete"), "Overdue")
Any help is appreciated, I feel like I'm missing something obvious. Thanks!
Best Answer
-
I found a solution in another post...here it is in case anyone is looking:
=IF(AND(OR(CONTAINS("Assigned", Status@row), CONTAINS("In-Progress", Status@row), CONTAINS("Awaiting more Information", Status@row)), TODAY() > [Due Date]@row), "Overdue", "")
Answers
-
See below how to use the OR function
=IF(AND(TODAY() > [Target End Date]@row, OR(Status@row <> "Complete", Status@row <> "Cancelled")), "Overdue")
-
Thanks @Leibel Shuchat , unfortunately this is not giving the desired behavior. With this formula it is ORing between the Date and the Status. (Date in past OR status not a or b) So I'm gettting 'Overdue' when either of those conditions are true. I really want 'Overdue' if the date is in the past AND status is not cancelled or not complete.
-
I found a solution in another post...here it is in case anyone is looking:
=IF(AND(OR(CONTAINS("Assigned", Status@row), CONTAINS("In-Progress", Status@row), CONTAINS("Awaiting more Information", Status@row)), TODAY() > [Due Date]@row), "Overdue", "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 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!