Help please

MD_Accel 23

I have a column that keeps returning errors….causing quite a bit of confusion and frustration.

The first threat formula is working as intended.

First milestone threat formula:

=IF(AND([Percent complete]@row <> 0.1, TODAY() > [Currentmilestone date]@row +14),"Red", IF(AND([Percent complete]@row < 0.1, TODAY() > ([Currentmilestone date]@row)), "Yellow","Green"))

Screenshot 2024-09-11 145425.png

The second milestone threat formula I am receiving the error to, I have the following:

=IF(AND([Percent complete]@row<>.2,TODAY()>[Second milestone date]@row+14),"RED", IF(AND([Percent complete]@row<.2,TODAY()>[Second milestone date]@row)),"YELLOW","GREEN"))

Jason Tarpinian

You got an extra close parenthesis before your "YELLOW" than you need, so you were closing out your IF statement before returning a true value:

=IF(AND([Percent complete]@row <> 0.2, TODAY() > [Second milestone date]@row + 14), "RED", IF(AND([Percent complete]@row < 0.2, TODAY() > [Second milestone date]@row), "YELLOW", "GREEN"))

Jason Tarpinian

You got an extra close parenthesis before your "YELLOW" than you need, so you were closing out your IF statement before returning a true value:

=IF(AND([Percent complete]@row <> 0.2, TODAY() > [Second milestone date]@row + 14), "RED", IF(AND([Percent complete]@row < 0.2, TODAY() > [Second milestone date]@row), "YELLOW", "GREEN"))

MD_Accel 23

Thank you so much @Jason Tarpinian, frustrating that I missed that and caused myself more work and grief but very thankful you were able to point it out so quickly!

Jason Tarpinian

Don't worry about it, I was able to find it quickly because I've been in your shoes WAAYY too many times! 😊

A method I use to catch it, is if you click through the formula, the helper window will show you where you're supposed to be. So like below, my cursor is clicked on "YELLOW" so the "value_if_true" should highlight if I had the formula written correctly.

MD_Accel 23

Oh my gosh! Thank you for the tip!!! That makes sense.

Question….and this may be a generic Smartsheet question.

My column is set to symbols exactly like the first column. But it's returning the color in text versus the colored symbol.

MD_Accel 23

=IF([Percent complete]@row > 0.2, "Green", IF(AND([Percent complete]@row <> 0.2, TODAY() > [Second milestone date]@row + 14), "Red", IF(AND([Percent complete]@row < 0.2, TODAY() > [Second milestone date]@row), "Yellow", "Green")))

I am receiving an #invalid operation….what did I misplace in my formula?

MD_Accel 23

Never mind! I figured out my error. My second milestone column was not set to being a date column, it was open text. once I changed this, the formula worked as intended.

Kelly Moore

Hey @MD_Accel 23

What color were you expecting, Green or Yellow? Whichever is the correct answer for you, add an equal sign to that term. For example, if it is Green then

=IF([Percent complete]@row >= 0.2, "Green", IF(AND([Percent complete]@row <> 0.2, TODAY() > [Second milestone date]@row + 14), "Red", IF(AND([Percent complete]@row < 0.2, TODAY() > [Second milestone date]@row), "Yellow", "Green")))

Does that work for you?

Kelly