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

  • DMH
    DMH ✭✭
    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

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @DMH

    See below how to use the OR function

    =IF(AND(TODAY() > [Target End Date]@row, OR(Status@row <> "Complete", Status@row <> "Cancelled")), "Overdue")

  • DMH
    DMH ✭✭

    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.

  • DMH
    DMH ✭✭
    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", "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!