Using IF formula to set conditions for RYGB ball color

Options
edited 12/09/19

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.

• Options

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

• ✭✭✭✭
Options

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!

Regards,

Maxim

• Options

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!