How to highlight overlapping date ranges (Excel 2007)

  1. Click "Home" tab
  2. Click "Conditional Formatting" button
  3. Click "New Rule.."
  4. Click "Use a formula to determine which cells to format"
  5. Type =SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 in "Format values where this formula is TRUE" window.
  6. Click "Format.." button
  7. Click "Fill" tab
  8. Select a color for highlighting cells.
  9. Click "Ok"
  10. Click "Ok"
  11. 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.

Download excel sample file for this tutorial.
highlight overlapping dates.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays