Automated Status Ball color change

Jeffrey H

I have the following formula shown below that changes the Status symbol (Gray/Green/Yellow/Red) based on the Phase/Risk/Change Implementation Date.  I tried to update it for a new Phase "Canceled" but I keep getting the error #UNPARSEABLE. 

 

Is this due to the fact that I'm trying to have both the Phase "Operationalize" & "Canceled" show as the Grey status ball?

 

Formula - Without Canceled (this one works)

=IF([At Risk]1, "Red", IF(Phase1 = "Operationalize", "Gray", IF(Phase1 = "Paused", "Yellow", IF(TODAY() - [Change Implementation Date]1 > 0, "Red", IF(TODAY() - [Change Implementation Date]1 > -7, "Yellow", "Green")))))

 

Formula - With Canceled Phase (this one doesn't work)

=IF([At Risk]1, "Red", IF(Phase1 = "Operationalize", "Gray", IF (Phase1 = “Canceled”, “Gray”, IF(Phase1 = "Paused", "Yellow", IF(TODAY() - [Change Implementation Date]1 > 0, "Red", IF(TODAY() - [Change Implementation Date]1 > -7, "Yellow", "Green")))))

Brian W

I think there are a few things happening here, including a missing last parenthesis and a space after IF. Also, I don't think Smartsheet likes the "smart quotes" around Canceled and Gray. Try this:

=IF([At Risk]1, "Red", IF(Phase1 = "Operationalize", "Gray", IF(Phase1 = "Canceled", "Gray", IF(Phase1 = "Paused", "Yellow", IF(TODAY() - [Change Implementation Date]1 > 0, "Red", IF(TODAY() - [Change Implementation Date]1 > -7, "Yellow", "Green"))))))

 

Brian W

Actually, I think the parentheses are correct.

Mike Wilday

I think Brian nailed it on the head. That space, and smartquotes will kill your formulas everytime. 

Jeffrey H

Thank you Brian, that worked!

Question, I notice the visual difference between your quotes around Canceled and Gray but how did you do that?

I press (  Shift + "  ) to produce the quotes, is that incorrect?

 

Thank you again.  

Brian W

The smart quotes are usually a result of copying a formula from a different program, like MS Word, and pasting into Smartsheet. If you use a plain text editor like Notepad or type the quotes directly in Smartsheet, you should be okay.