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

Options

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”)))))

Tags:

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Options

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"))))

• Employee
Options

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