### 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.