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!

Find more posts tagged with

Accepted answers

DMH

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

All comments

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.