Using the OR and AND Function together

I have a checkbox that I need to have checked if the Due Date Field is less than today and the Status does not equal Completed or No Longer Required. I've tried multiple combinations of formulas and I have not been able to figure it out. I need some help. Here is the formula that I currently have.

=IF(OR(AND([Due Date]15 < TODAY(), AND(NOT(Status15 = "Completed", Status15 = "No Longer Required")))), 1, 0)

Thanks,

Robert

Find more posts tagged with

Formulas

Accepted answers

All comments

Kelly Drake

@rpyonker The OR and the first AND basically cancel each other out.

You need to change to this:

=IF(AND([Due Date]@row < TODAY(), OR(NOT(Status@row = "Completed", Status@row = "No Longer Required"))), 1, 0)

Also note the @row optimization for your formula. https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

rpyonker

Thank you Kelly for your answer. I put in the formula as you suggested but it didn't work. I did finally modify it by adding OR(NOT( for each of the options of Completed or No Longer Required and that worked. I did however try to add a third one but then I got an unparsable error. Is there a limit on how may times you can use OR(NOT( in a formula?

=IF(AND([Due Date]@row < TODAY(), OR(NOT(Status@row = "Completed", OR(NOT(Status@row = "No Longer Required"))))), 1, 0)

Also, thank you for the time on optimizing the formula with the @row reference.

Thank you for you help.

Kelly Drake

The second OR is the problem in your new formula and the NOT segments weren't closed in the right spaces

=IF(AND([Due Date]@row < TODAY(), OR(NOT(Status@row = "Completed"), NOT(Status@row = "No Longer Required"){*Add a comma and then any additional conditions of the OR here, otherwise delete everything between these curly braces*})), 1, 0)

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.