excel conditional formating with multiple date ranges

0
votes

I have looked for proper formula that would solve my problem but I couldn't find anything. I have a table with multiple date ranges and I want to highlight all dates in my calendar between these ranges. I've tried to use formula AND 

=AND(F5>=$A$6,F5<=$B$6)

however the formula highlights only dates between 1st range. I tried to put array ($A6:$A$9 and $B6:$B$9) but it doesn't work. 

       Column A     Column B
row 6 | 05/01/2018 | 12/01/2018  
row 7 | 03/04/2018 | 16/04/2018  
row 8 | 06/05/2018 | 17/05/2018  
row 9 | 01/11/2018 | 05/11/2018

My calendar starts in cell F5 and ends in AP16.

Regards, Adrian

2
excelexcel-formulaconditional-formatting
Is that a typo in B8? Should that year be 2018 also? - Ron Rosenfeld
yes there is a typo, t was just an example, formulas provided below works perfectly fine - Adrian

2 Answers

2
votes

You need to wrap your AND's within an OR:

=OR(AND(F5>=$A$6,F5<=$B$6),AND(F5>=$A$7,F5<=$B$7), AND(...))

or, in a more compact but equivalent form:

=SUMPRODUCT((F5>=$A$6:$A$9)*(F5<=$B$6:$B$9))

or

=OR((F5>=$A$6:$A$9)*(F5<=$B$6:$B$9))

Each of the equality arrays returns an array of 1's or 0's. Multiplying them together is the equivalent of AND and will return a 1 if and only if both values in the same position are TRUE. Adding the arrays (the equivalent of OR) will then show if any result is a 1.

Although Excel 2016 will accept an OR in the conditional format formula, I seem to recall that some earlier versions will not, hence I have also supplied the equivalent SUMPRODUCT formula.

1
votes

Or once again you can use countifs

=COUNTIFS($A$6:$A$10,"<="&F5,$B$6:$B$10,">="&F5)