Nested OR, AND, IF

Hi All,

I am only a week 1 user so forgive if this is a little basic. I have a flag/no flag column in my sheet that currently indicates whether a report is overdue based on the status of the project, due date of the report, todays date and whether a report has been submitted (entered in the shseet as a date). The format currently works like this

=IF(AND([Interim Report 1 Due]@row < TODAY(), [IR 1 Received]@row = "", Status@row = "in progress"), 1, 0)

I would like to add an OR criteria (Status@row = "completed") to show both In Progress and Completed projects that have overdue reports.

I cannot seem to work out where to slip this little criteria and the operator in. Any help would be appreciated.

Find more posts tagged with

Accepted answers

All comments

Mark Cronk

Hi @Brandon Bear ,

Try:

=IF(OR(Status@row = "completed", AND([Interim Report 1 Due]@row < TODAY(), [IR 1 Received]@row = "", Status@row = "in progress")), 1, 0)

Work?

Mark

Brandon Bear

https://community.smartsheet.com/discussion/comment/284679#Comment_284679

Okay, so after putting this in, the formula still doesn't return a 1 (flag) if the Status is Complete

Heather Duff

@Brandon Bear , are the completed statuses in your status column listed as "Complete" or "Completed"? Your formula will need to contain an exact match to the text it's 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.