I have 4 conditional formatting statements that I need to combine and make in to 1 if this is possible. =AND(TODAY()>=$C$8,TODAY()<=$D$8)

=AND(TODAY()>=$J$8,TODAY()<=$K$8)

=AND(TODAY()>=$Q$8,TODAY()<=$R$8)

=AND(TODAY()>=$X$8,TODAY()<=$Y$8)What this does is highlight B8 if todays date is between any of the dates that are in the other cells in each of the statements. My problem is the company is useing 2003 and I can only put 3 CFs in a cell. I was thinking I could use an AND/OR statement but for the life of me I cannot think of how to right it.

Not sure this is what you want, but try a Defined Name Take your four AND() formulas and tuck them away in a

part of your sheet that no one is going to bother, like column XAOnce you have all four formulas in XA(1) - XA(4)

Hightlight all four cellsNext, Click on the Name Box, which is is directly above the "A1" cell.

Now, type the new name for the cell(s), for example: MyDateList

Press Enter.You can now use the Defined Name, MyDateList in your Conditional Formatting.

If I understand you correctly, all four conditons must be TRUE

so simply putting: =MyDateList as the formula should work.MIKE

so what you are saying is if any 1 of your conditional and statements is true then apply the formatting. If that is the case you are bang on with you wanting to use AND/OR statements. You can combine the whole thing into one line. =OR(AND(TODAY()>=$C$8,TODAY()<=$D$8),AND(TODAY()>=$J$8,TODAY()<=$K$8),AND(TODAY()>=$Q$8,TODAY()<=$R$8),AND(TODAY()>=$X$8,TODAY()<=$Y$8))

Start with your OR statement then place each one of your AND statement separate by a comma. And if you are like me, dont forget to close with a )

So in summary, if any one of your AND conditions is true, the whole OR statement is true and the formating will be applied. This conditional formating is applied in cell you want to get highlighted. if you are going to copy the conditional formating be aware of your $ usage.

Thank you very much this is what I needed. My mistake was putting the AND first then the or and not even putting the AND in the other locations. Thank you for putting me straight again.

Ask Your Question

Weekly Poll

Do you think Europe's new data protection laws will affect you?

Discuss in The Lounge

Poll History