Receiving error when using If/And
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.
Best Answer
-
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")
Answers
-
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")
-
@Christina Lam that clears the incorrect argument but leaves me with #Unparsable error
-
#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?
-
It is =IF([Date Opened]@row = TODAY(-2), IF([Status]@row <> "New - Review"), "Red"))
I added the ")" like you said.
-
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")
-
@Christina Lam - Thank you that worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!