Table of Contents
Find overlapping date ranges
Formula in cell E6:
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.
See an explanation of this formula here:
Highlight overlapping date ranges using conditional formatting in excel
Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Find overlapping date ranges with criterion
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!
Array formula in cell F2:
Download excel *.xlsx file