Table of Contents

Find overlapping date ranges

Find overlapping date ranges with criterion

Find overlapping date ranges

Formula in cell E6:

=SUMPRODUCT((B6< =$C$6:$C$12)*(C6>=$B$6:$B$12))>1

Copy cell E6 and paste it down as far as needed.

Here is a picture of the date ranges. Jeff'´s and Shaun´s date ranges overlap.

overlapping date ranges2

See an explanation of this formula here:
Highlight overlapping date ranges using conditional formatting in excel

Download excel sample file for this tutorial.
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

Find overlapping date ranges with criterion

Brett asks:

Hey Oscar,

I tried this and I can't get accurate results with the data set I'm working with.

As an example, how would the formula work if I wanted to find the overlap between the start 2/end 2 dates versus start 1/end 1 dates?

Title - Start 1 End 1 Start 2 End 2 Overlap?
Titans 6/1/14 5/31/15 6/1/14 6/30/14
Titans 6/1/16 5/31/17 8/1/14 8/31/14
Titans 6/1/18 5/31/19 6/1/17 5/31/18
Titans 7/1/21 6/30/23 6/1/19 2/29/20

In the above example, in the "overlap" cell, it should say "yes" for row 1 and row 2, and "no" for row 3 and row 4.

Thanks so much for the help!

Answer:

Array formula in cell F2:

=SUMPRODUCT((A2=$A$2:$A$5)* ((B2<=$E$2:$E$5)* (C2>=$D$2:$D$5)+ (D2<=$C$2:$C$5)* (E2>=$B$2:$B$5)))>0

Download excel *.xlsx file

Find overlapping date ranges with criterion.xlsx