Help please
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"))
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"))
Best Answer
-
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 - Sevan Technology
Smartsheet Aligned Partner
Answers
-
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 - Sevan Technology
Smartsheet Aligned Partner
-
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!
-
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.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
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.
-
=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?
-
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.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!