Assistance with IF, AND, OR statements

=IF([Due Date]@row > TODAY(), IF(AND(Status@row = "In Progress", Status@row = "New", Status@row = "Not Started"), "Yes", "No")))

The goal is to flag items for escalation based on the due date and status. If the due date is in the past and the status of the risk or decision is in progress, not started, or new then I need to display Yes in the escalation column.Anything complete, deferred, cancelled should display a no.

Find more posts tagged with

Project Management

Accepted answers

JenH

Fixed it for future reference

=IF(OR([Due Date]@row > TODAY(), Status@row = "In Progress", Status@row = "New", Status@row = "Not Started"), "Escalate", "No")

All comments

Jgorsich

Just change your "and" to an "or". "And" requires ALL conditions to be true to result in a true outcome, "Or" requires ANY of the conditions to be true to result in a true outcome. Since you'll never have a project that is both "in progress" AND "not started" AND "new", your "and" test will fail. You want everything that is "in progress" OR "not started" OR "new" - use an "or" test.

JenH

Thank you, I am still running into no results being returned when I changed the AND to OR. Update =IF([Due Date]@row > TODAY(), IF(OR(Status@row = "In Progress", Status@row = "New", Status@row = "Not Started"), "Yes", "No")))

Jgorsich

Try testing it in pieces. First just make it =if([Due Date]@row>today(),"yes","no") and see if you are getting a mix of yes and no answers. then try adding one more condition like this: =IF([Due Date]@row > TODAY(), IF(OR(Status@row = "In Progress"), "yes","no"),"past due")and see whether or not everything gets categorized correctly. Once that is working correctly, try adding another condition into the "or" statement.

JenH

I did start with the =if([Due Date]@row >today(),"yes","no")which worked but the next conditions so not work appropriately.

JenH

Fixed it for future reference

=IF(OR([Due Date]@row > TODAY(), Status@row = "In Progress", Status@row = "New", Status@row = "Not Started"), "Escalate", "No")

Jgorsich

This will flag EVERYTHING that is due in the future, btw. If that is what you want, excellent, but it might not be what you are looking for.

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.