Conditional Formatting Using "OR"

[Robbie]

I'm wanting to use multiple conditions using "OR" for conditional formatting.
Here's the criteria for percentage of completion of 6 and 9 months from
entering training:

6 months from entering training
0-25% Red
26-50% Yellow
51-100% Green

9 months from entering training
0-50% Red
51-75% Yellow
76-100% Green

Here's an example of a formula that didn't work:

=OR(B3+182.5<=TODAY()-182.5,AI3<=25%)

B3 is the date entered training and AI3 is the percentage of completion and
where I'm wanting to have the background change to match the color criteria.
This 1st formula didn't work, so I couldn't continue.

Thanks,
Robbie

 

[Pete_UK]

Well, Robbie, you are adding 6 months onto the starting date and then
taking 6 months off today's date, so I don't think it will work. I
think you need a formula like this:

=OR(AND(B3+182.5<=TODAY(),AI3<=25%),AND(B3+273.75<=TODAY(),AI3<=50%))

and use this to turn the cell red. For your second condition you will
have a very similar formula:

=OR(AND(B3+182.5<=TODAY(),AI3<=50%),AND(B3+273.75<=TODAY(),AI3<=75%))

and set the cell to yellow. Then for your third condition:

=OR(AND(B3+182.5<=TODAY(),AI3<=100%),AND(B3+273.75<=TODAY(),AI3<=100%))

and choose green.

You can use the Format Painter to apply this CF to other cells in the
same column.

Hope this helps.

Pete

 

[Robbie]

Thanks, Pete! The formulas and color-coding are functioning properly!

Robbie

 

[Pete_UK]

You're welcome, Robbie - glad to hear it.

Pete