Nested and/or formula

JHone

I am trying to set an at risk flag when a date is in the past and the status is not cancelled or complete. I have used the following formula in Excel and it works, however, I cannot make it work in Smartsheet. Any advice?

=IF(AND([Due Date]@row < TODAY(), OR(Status@row="Cancelled", Status@row="Complete")), 0, 1))

I did get the following formula to work for just one variable for the status, but I am still unable to get it to work when I try to make it flag if the date is in the past and if the status is anything other than Complete or Cancelled.

=IF(AND([Due Date]@row < TODAY(), Status@row<>"Complete"), 1, 0)

JHone

That leaves one more closing parenthesis than there are opening parentheses. I figured out that I can get the result I want with the following formula since the status field is a drop down list and is limited to the selections in the drop down list. It is a longer formula and wouldn't work if there was not a limited selection for the status field, but meets my needs.

=IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold", Status@row = "")), 1, 0)

TG_Controls_PM

The below says, If the actual ship date is blank, the cell equals "in-progress", or if the actual ship date is before today, the cell equals "complete".

=IF(ISBLANK([Actual Ship Date]@row), "in-progress", IF([Actual Ship Date]@row < TODAY(), "complete", "in-progress"))

You can also set this to be within 10 days of TODAY to flip a flag risk.

Genevieve P.

Hi @JHone

It looks like you're just missing a closing parentheses in the right place. You need to close off the AND before stating the 0

Try this:

=IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Cancelled", Status@row = "Complete"))), 0, 1)

Cheers,

Genevieve

JHone

That leaves one more closing parenthesis than there are opening parentheses. I figured out that I can get the result I want with the following formula since the status field is a drop down list and is limited to the selections in the drop down list. It is a longer formula and wouldn't work if there was not a limited selection for the status field, but meets my needs.

=IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold", Status@row = "")), 1, 0)