Adding to a Complex Formula

Anthony Barthelemy

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

Paul Newcome

You could just wrap it in an IFERROR statement.

=IFERROR(DATEONLY([Actual Date]6) - [Planned Date]6), "")

Paul Newcome

Happy to help! 👍️

Paul Newcome

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.

Anthony Barthelemy

I want to add the Color, not the ball.

Paul Newcome

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

Anthony Barthelemy

After

Paul Newcome

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

Anthony Barthelemy

I get the same results but with no color.

Anthony Barthelemy

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

Anthony Barthelemy

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

Paul Newcome

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.

Anthony Barthelemy

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)

Paul Newcome

You could just wrap it in an IFERROR statement.

=IFERROR(DATEONLY([Actual Date]6) - [Planned Date]6), "")

Anthony Barthelemy

Excellent, Thanks!

Paul Newcome

Happy to help! 👍️