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

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.

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!