# Help with a formula for: IF field1 = A AND field2 = x or y or z then

Options
✭✭

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!

• ✭✭
Options

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", "")

• ✭✭✭✭✭✭
Options

See below how to use the OR function

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

• ✭✭
Options

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.

• ✭✭