Author: Oscar Cronquist Article last updated on July 01, 2018

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that overlaps.

The first and fourth record are highlighted because the date ranges overlap. 1/4/2010 - 1/8/2010 overlaps with 1/7/2010 - 1/9/2010.

Here are the steps needed to create a conditional formatting formula

  1. Select cell range B6:D12.
  2. Click "Home" tab.
  3. Click "Conditional Formatting" button.
  4. Click "New Rule..".
  5. Click "Use a formula to determine which cells to format".
  6. Type =SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 in "Format values where this formula is TRUE" window.
  7. Click "Format.." button.
  8. Click "Fill" tab.
  9. Pick a color. This color is used to highlight records that overlap.
  10. Click "OK".
  11. Click "OK".
  12. Click "OK".

Explaining conditional formatting formula

=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1

Step 1 - Filter records where ($C6<=$D$6:$D$12) is TRUE

$C6<=$D$6:$D$12 returns an array of TRUE and/or FALSE if date in cell C6 is smaller or equal to each date in $D$6:$D$12.

All dates in$D$6:$D$12 are larger than $C6 so the returning array is (TRUE, TRUE, TRUE,TRUE, TRUE, TRUE,TRUE)

Step 2 - Filter records where ($D6>=$C$6:$C$12) is TRUE

($D6>=$C$6:$C$12) returns an array of TRUE and/or FALSE if date in cell D6 is bigger or equal to each date in $C$6:$C$12.

The returning array is (TRUE, FALSE, FALSE,TRUE, FALSE, FALSE,FALSE)

Step 3 - Putting it all together

($C6<=$D$6:$D$12)* ($D6>=$C$6:$C$12)) returns the following array (1,0,0,1,0,0,0).

That means array number one and four is overlapping C6:D6) but in this case we just want to know if any date range is overlapping.

=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12)) sums the array (1,0,0,1,0,0,0) and returns 2.

If the formula returns a number bigger than one there is at least one overlapping date range.

=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 returns TRUE or FALSE.

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

Download excel *.xls file

highlight overlapping dates.xls