Using IF formula to set conditions for RYGB ball color


Hi there!

I have been trying to set conditions with a formula to change the color of a RYGB ball based on two columns, the percent complete and the due date. I wish to use the RYGB balls and not just change the color of the cell, which is why I'm using formulas instead of the conditional formatting. I keep receiving the UNPARSEABLE error. 

Below is my current formula. Not sure if I have my status circle column setup incorrectly, or if there's a flaw in my formula. Tips on what I might be missing would be appreciated! 

=IF([Percent Complete]6 < 100 AND(TODAY()>DATEONLY([Due Date]6)), "Red", IF([Percent Complete]6 < 85 AND(TODAY(-14)=DATEONLY([Due Date]6)), "Yellow", IF([Percent Complete]6=100, "Blue", IF([Percent Complete]6=100 AND(TODAY()=DATEONLY([Due Date]6)), "Green", IF(TODAY(14)<DATEONLY([Due Date]6)), "Green"))))

*Percent Complete and Due Date are columns with general number formatting and MM/DD/YY formatting, respectively. I am working on row 6, hence the [Column Name]6 notation. 



  • Madison_Gipson

    Update: I think it might be something flawed in my Date conditionals because the Percent complete conditions work fine on their own.

  • Maxim Lobko
    Maxim Lobko ✭✭✭✭

    the issue is in the syntax of AND function. Here is a corrected formula:

    =IF(AND([Percent Complete]@row < 100, (TODAY() > DATEONLY([Due Date]@row))), "Red", IF(AND([Percent Complete]@row < 85, (TODAY(-14) = DATEONLY([Due Date]@row))), "Yellow", IF([Percent Complete]@row = 100, "Blue", IF(AND([Percent Complete]@row = 100, (TODAY() = DATEONLY([Due Date]@row))), "Green", IF(TODAY(14) < DATEONLY([Due Date]@row)), "Green"))))

    BTW, I replaced @6 by @row, so the formula can be easily copy/pasted into other rows.

    I hope, it helps!



  • Madison_Gipson

    This fixed it, thank you!

    For anyone referencing this in the future, I also fixed the formula details so the output was fully correct and consistent: 

    =IF([Percent Complete]@row = 100, "Blue", IF(AND([Percent Complete]@row < 100, (([Due Date]@row) < TODAY())), "Red", IF(AND([Percent Complete]@row < 100, (([Due Date]@row) <= TODAY(14))), "Yellow", IF(AND([Percent Complete]@row < 100, (([Due Date]@row) > TODAY(14))), "Green"))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!