Find overlapping date ranges in excel
Table of Contents
Find overlapping date ranges with criterion
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.
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:
Download excel *.xlsx file
Find overlapping date ranges with criterion.xlsx
Related posts:
Filter overlapping dates from date ranges in excel
Prevent users from entering overlapping date and time ranges
Highlight overlapping date ranges using conditional formatting in excel
Recommended categories
Chandoo has posted a much shorter better formula: http://chandoo.org/wp/2010/06/01/date-overlap-formulas/
Oscar, could you explain the COUNT($B$6:$B$12)-1 bit in the formula...
Peter,
Here is a shorter formula: =SUMPRODUCT((C6< =$D$6:$D$12)*(D6>=$C$6:$C$12)*(ROW(C6)<>ROW($C$6:$C$12)))>0. Copy cell and paste it down as far as needed.
The formula returns TRUE if overlapping.
Peter,
=SUMPRODUCT((C6< =$D$6:$D$12)*(D6>=$C$6:$C$12))>1 is shorter.
Copy cell and paste it down as far as needed.
The formula returns TRUE if overlapping.
Thanks Oscar...... appreciate your prompt feedback
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!
Brett
Brett,
read this:
Find overlapping date ranges with criterion
Hi Oscar,
Oh my, this is so close, but is just shy of solving the problem.
The problem I'm encountering if is there are multiple "titles".
In the example below, the formula has to find a match in the "title" column and then look at all start and end dates for the matched title to see if there is any cross over.
Here's another example:
Name Start 1 End 1 Start 2 End 2 Overlap?
Titans 6/1/14 5/31/15 6/1/14 6/30/14 TRUE
Titans 6/1/16 5/31/17 8/1/14 8/31/14 TRUE
Titans 6/1/18 5/31/19 6/1/17 5/31/18 FALSE
Titans 7/1/21 6/30/23 6/1/19 2/29/20 FALSE
Campers 10/1/15 9/30/16 10/1/14 9/30/15
Campers 10/1/17 9/30/18 9/1/16 9/30/17
Campers 11/1/21 4/30/22 10/1/17 6/30/20
Dark Horse 6/1/16 11/30/16 12/1/14 11/30/15
Dark Horse 12/1/16 11/30/17 12/1/15 5/31/16
Dark Horse 12/1/18 11/30/19 12/1/16 11/30/18
Gang 4/1/17 3/31/18 4/1/15 3/31/16
Gang 4/1/18 3/31/19 4/1/16 4/30/18
Ants 2/1/17 1/31/18 #N/A #N/A
Ants 2/1/19 1/31/20 #N/A #N/A
Again, this is way out of my league and thanks so much for putting your excel expertise to work!
Brett
Brett,
I am not sure I am following, what is the desired outcome in column F?
Hi Oscar,
When I copy (or auto fill) in the formula down in row F, it doesn't calculate the way your example does above and (except for rows 2 and 3), my results are "false" for rows 4 through 13.
Since this is an array formula, is there a special way I have to to copy the formula.
Or, can you post the excel version of your example above so I can look at the formula?
Thanks!
Brett
Brett,
expand the cell references to this:
=SUMPRODUCT((A2=$A$2:$A$13)*((B2< =$E$2:$E$13)*(C2>=$D$2:$D$13)+(D2< =$C$2:$C$13)*(E2>=$B$2:$B$13)))>0
Hi again,
I should also add that my data contains unique "titles" and my goal is for the formula to look at the differing start and end dates for each like title.
Thanks!
Brett
hi brett did u get the answer for your question as i am having similar trouble
Hi Oscar ,
I have similar problem as brett. I also want to check only for that ranges which have similar value for titles. Basically I want to compare overlapping only for those rows which have same value of say column1
Puneet,
Conditional formatting formula:
=SUMPRODUCT(($A2=$A$2:$A$9)*($B2< =$C$2:$C$9)*($C2>=$B$2:$B$9))>1