Conditional Format Highlight Cells Based on Date Range that corresponds to recurring criteria

[mattfj]

Hi,

I am trying to put together a file that shows the date ranges that trucks will be unavailable because of assigned deliveries. The idea is that any date that any cell that is not highlighted shows when they are available to take cargo. I am using the following formula in the conditional formatting:

=IF(COUNTIF($A$13:$A$16,$A$3)>0,AND(E$1>=$B13,E$1<=$C13),"")

As you can see it is highlighting the date range for each truck but it is only highlighting the date range for the first occurrence. I am trying to get it to highlight both of the date ranges that are listed for Truck 1. Please help.

 

[Fluff]

How about

=AND($A3<>"",SUMPRODUCT(($A$13:$A$17=$A3)*($B$13:$B$17<=E$1)*($C$13:$C$17>=E$1)))

 

[mattfj]

How about

=AND($A3<>"",SUMPRODUCT(($A$13:$A$17=$A3)*($B$13:$B$17<=E$1)*($C$13:$C$17>=E$1)))

Sir,

You are a genius. I should have specified that my problem was much bigger than the small example that I gave. However, the solution that you offered me put me on the right track and I was ultimately able to use this to solve my problem. Thank you so much for your help.

Regards,

Matt

 

[Fluff]

You're welcome & thanks for the feedback.