Conditional Formatting Using "OR"
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 -- Michael Robinson |
Conditional Formatting Using "OR"
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 On Jun 25, 12:16*am, Robbie wrote: 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 -- Michael Robinson |
Conditional Formatting Using "OR"
Thanks, Pete! The formulas and color-coding are functioning properly!
Robbie -- Michael Robinson "Pete_UK" wrote: 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 On Jun 25, 12:16 am, Robbie wrote: 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 -- Michael Robinson |
Conditional Formatting Using "OR"
You're welcome, Robbie - glad to hear it.
Pete On Jun 25, 4:19*pm, Robbie wrote: Thanks, Pete! *The formulas and color-coding are functioning properly! Robbie -- Michael Robinson |
All times are GMT +1. The time now is 09:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com