Health Ball Color Base on Start Date, End Date, and % Complete

Tanjha VanHoosier

Hello,

I am relatively new to Smartsheets. I am trying to write the following formula but continue to hit an "UNPARSEABLE" Error. I am sure I am missing something but cannot find the issue. I would appreciate assistance.

This is the core rule I am trying to draft.

Here is my formula attempt:

IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF([Start Date]@row < TODAY(), "Green", IF([End Date]@row > TODAY(-7) AND [% Complete] < .75, “Yellow”, IF([START Date]@row < TODAY () AND [% Complete] = 0, “Yellow”)))))

Thank you for your help.

Genevieve P.

Amazing formula! Glad you could figure it out 🙂

Genevieve P.

Hi @Tanjha VanHoosier

You were very close!

Here's the full formula for your requirements:

IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row > TODAY(-7), [% Complete]@row < 0.75), “Yellow”, IF(AND([START Date]@row < TODAY(), [% Complete] = 0), “Yellow”)))))

The issue was essentially where the AND was placed - it should go right after the IF and it needs (these) around the two requirements.

You can even tighten up the formula by using OR instead of the final IF:

IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF([Start Date]@row > TODAY(), "Green", IF(OR(AND([End Date]@row > TODAY(-7), [% Complete]@row < 0.75), AND([START Date]@row < TODAY(), [% Complete] = 0)), “Yellow”))))

Let me know if this works for you!

Cheers,

Genevieve

Tanjha VanHoosier

Thank you for your help in understanding the AND statement. That was very helpful. However, I am still hitting an UNPARSEABLE error when I add the row numbers in the sheet. I also tried typing it all out manually to avoid any copy errors but still am hitting issues. I must still be typing something incorrectly.

AND Statement

IF([% Complete]133 = 1, "Blue", IF([End Date]133 < TODAY (), "Red", IF([Start Date]133 > TODAY (), "Green", IF(AND([End Date]133 > TODAY (-7), [% Complete]133 < 0.75),"Yellow", IF(AND([Start Date]133 < TODAY (), [%Complete]133 = 0), "Yellow")))))

OR AND Statement

IF([% Complete]134 = 1, "Blue", IF([End Date]134 < TODAY (), "Red", IF([Start Date]134 > TODAY (), "Green", IF( OR(AND([End Date]134 > TODAY (-7), [% Complete]134 <0.75),AND([Start Date]134 < TODAY (), [% Complete]134 = 0)), "Yellow"))))

Genevieve P.

Hi @Tanjha VanHoosier

It looks like there are some spaces in between your TODAY function and the parentheses (). You will want to have the open parentheses ( immediately after TODAY.

I also notice that one of your % Complete references has no space between the % and Complete... this will need to match whatever your column name is (either %Complete or % Complete).

Try this:

=IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row > TODAY(-7), [% Complete]@row < 0.75), "Yellow", IF(AND([Start Date]@row < TODAY(), [% Complete]@row = 0), "Yellow")))))

Notice that I replaced all your row references (133) with @row, so that the formula knows it only needs to look within that current row and doesn't have to search the sheet for row 133. I also have consistent spacing for my column names, and no spaces between TODAY and the ().

If this doesn't work, it would be helpful to have a screen capture of your sheet with the formula open in a cell so I can compare column names, but please block out any sensitive data!

Cheers,

Genevieve

Tanjha VanHoosier

Thank you so much! I did not know about the true function of @row. I was able to get it figured out. It appeared that my Start Date column name had an extra space in the title. So that solved it. Here is the final formula for anyone else who would like to use it.

=IF([% Complete]@row = 1, "Blue", IF([End Date]@row <= TODAY(), "Red", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row > TODAY(-7), [% Complete]@row < 0.75), "Yellow", IF(AND([Start Date]@row < TODAY(), [% Complete]@row = 0), "Yellow", IF(AND([% Complete]@row > 0.75, [End Date]@row > TODAY(-7)), "Green", "Green"))))))

Genevieve P.

Amazing formula! Glad you could figure it out 🙂