# Adding to a Complex Formula

Good morning All:

I am trying to add RYG to my Formula. Here are my conditions:

If 15+ Days Behind Schedule = Red

If Between 5-15 Days Behind Schedule = Yellow

If 0 or Ahead of Schedule = Green

Current Formula without RYG:

=IF(ISBLANK([Actual Date]@row), "", IF(AND(ISBLANK([Planned Date]@row), ISDATE([Actual Date]@row)), "Start Date Missing", IF([Planned Date]@row - [Actual Date]@row < 0, ABS([Planned Date]@row - [Actual Date]@row) + " Day(s) Behind Schedule", IF([Planned Date]@row - [Actual Date]@row > 0, ABS([Planned Date]@row - [Actual Date]@row) + " Day(s) Early", IF([Planned Date]@row - [Actual Date]@row = 0, "0 Variance, On Schedule")))))

Thanks

• How exactly are you trying to incorporate RYG? You will not be able to show the colored ball AND additional data in the same cell at the same time.

• I want to add the Color, not the ball.

• Ok. And do you want to add it before or after the text that is currently being generated?

• In that case, all you should have to do is add a nested IF to the end of your existing formula.

=ORIGINAL FORMULA + IF([Planned Date]@row - [Actual Date]@row <= -15, " Red", IF([Planned Date]@row - [Actual Date]@row <= -5, " Yellow", IF([Planned Date]@row - [Actual Date]@row >= 0, " Green")))

• I get the same results but with no color.

• Disregard! I was expected the cell to turn Green. not the actual word Green

• Thanks! I may be able to use this type of formula for something else.

• Ah. If you were wanting the cell to turn RYG, you would need to set up a column that tracks the variance then use conditional formatting based on that to change the cell color.

• How do I remove an error from the formula when I do not have a date in the Plan or Actual Column? I treid ISBlank but unsuccessful.

Current Formula:

=DATEONLY([Actual Date]6) - [Planned Date]6)

• Excellent, Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!