Question on Function Syntax using IF, AND, and OR

I am trying to get an At Risk check box to return a red flag if there is a date in End Date and that date is earlier than the curent date and the Status cell shows one of three statuses. Can't quite get the formula logic. Any suggestions would be wonderful. Thanks!

Desired results:

End Date is blank = no flag

End Date is not blank and:

End Date is greater than current date = no flag

End Date is less than current date and:

Status is any of "Complete", "Executed", or "Recorded" = flag

Current attempt:

=IF(AND(NOT(ISBLANK(End Date@row))), (AND(End Date@row < TODAY()), (OR(NOT(Status@row = "Complete")), (NOT(Status@row = "Executed")), (NOT(Status@row = "Recorded")))), 1, 0)

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=IF(AND([End Date]@row <> "", [End Date]@row < TODAY(), Status@row <> "Complete", Status@row <> "Executed", Status@row <> "Recorded"), 1)

All comments

Paul Newcome

Try this:

=IF(AND([End Date]@row <> "", [End Date]@row < TODAY(), Status@row <> "Complete", Status@row <> "Executed", Status@row <> "Recorded"), 1)

DKazatsky2

Hi @MLK ,

See if this works for you.

=IF(AND([End Date]@row <> "", [End Date]@row < TODAY(), OR(Statuts@row = "Complete", Statuts@row = "Executed", Statuts@row = "Recorded")), 1)

Hope this helps,

Dave

MLK

@Paul Newcome That worked perfectly, and taught me a lot about how the formulas work. Thanks!!!

Paul Newcome

Happy to help. 👍️

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.