Nested OR, AND, IF

Brandon Bear

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.

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.