Receiving error when using If/And

Christopher Pretty

I have a sheet with the following columns, Date Opened, New - Review Date, Working Date, Alert column, and Status.

New-Review and Working Dates are automated by the system when the Status changes to one of those options it records the date.

I am trying to write a formula to fit the below scenarios:

If one day has passed and the status has not changed to New - Review then Alert turns Yellow

If 2 days have passed and the status has not changed to New - Review then Alert turns Red

Then I would like basically the same formula to raise that same red dot when the status hasn't changed to Working within 9 days from the Date Opened.

I tried to write the first if statement but I am getting an incorrect argument set (=IF(AND([Date Opened]@row = TODAY(-2), Status@row <> "New - Review", "Red"). I tested each if statement individually and they worked, just not when I put them together.

Christina09

@Christopher Pretty

This is your original formula: =IF(AND([Date Opened]@row = TODAY(-2), Status@row <> "New - Review", "Red")

I meant the close bracket after "New - Review")

Use this: =IF(AND([Date Opened]@row = TODAY(-2), Status@row <> "New - Review"), "Red")

Christina09

Hi @Christopher Pretty

The formula you had there is missing a close bracket after "New - Review". Smartsheet color code the bracket so you know which function is incomplete.

=IF(AND([Date Opened]@row = TODAY(-2), Status@row <> "New - Review"), "Red")

Christopher Pretty

@Christina Lam that clears the incorrect argument but leaves me with #Unparsable error

Christina09

@Christopher Pretty

#UNPARSEABLE

Cause

The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Resolution

Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

Can you copy and paste the formula?

Christopher Pretty

@Christina Lam

It is =IF([Date Opened]@row = TODAY(-2), IF([Status]@row <> "New - Review"), "Red"))

I added the ")" like you said.

Christina09

@Christopher Pretty

This is your original formula: =IF(AND([Date Opened]@row = TODAY(-2), Status@row <> "New - Review", "Red")

I meant the close bracket after "New - Review")

Use this: =IF(AND([Date Opened]@row = TODAY(-2), Status@row <> "New - Review"), "Red")

Christopher Pretty

@Christina Lam - Thank you that worked!