At Risk Flag when Past Due and is not one of multiple status options (drop down)

robzas

I am trying to come up with function that raises flag if past the due date and status is not: 'CANCELLED', 'Customer Testing', 'Pending Sign Off', or ' SIGNED OFF'.

I have tried:

=IF(OR([Current Status]@row <> "CANCELLED", [Current Status]@row <> "Customer Testing, [Current Status]@row <> “Pending Sign Off”, [Current Status]@row <> “SIGNED OFF), 0, IF([Expected End Date]@row - TODAY() <= 7, 1, 0))

Paul Newcome

Sorry about that. Need to delete one of the closing parenthesis from the end.

Paul Newcome

A couple of things...

You want to use an AND function instead of OR.

You are missing some quotes after two of the text strings.

The slanted quotes are called "smart quotes" which (ironically enough) Smartsheet does not recognize. You will need to retype those directly in the sheet to get the quotes that are straight up and down.

All Criteria should be nested in the single AND statement.

Give this a try...

=IF(AND([Current Status]@row <> "CANCELLED", [Current Status]@row <> "Customer Testing", [Current Status]@row <> "Pending Sign Off", [Current Status]@row <> "SIGNED OFF", [Expected End Date]@row - TODAY() <= 7), 1, 0))

Highlighted in bold above are all of the changes.

robzas

Thank you Paul - I still get #UNPARSEABLE when I insert that in....

Paul Newcome

Sorry about that. Need to delete one of the closing parenthesis from the end.

robzas

Thank you! That worked great - appreciate the help!

Paul Newcome

Happy to help. 👍️