## Identify overlapping date ranges

*Article updated on November 21, 2017*

**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**

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 24 Responses to “Identify overlapping date ranges”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Chandoo has posted a much shorter better formula: https://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

Good stuff Oscar!

I have a slightly different situation like Puneet's where I have 2 arbitrary reporting dates which I want to apply to a data set like the one you have there. But instead of returning TRUE/FALSE, I need to return the total count of days from the data set which fall between the 2 reporting dates. The output needs to be 1 value, rather than one per row. Struggling to get my head around this!

TC,

Is this what you are looking for?

Hi Oscar, This is really helpful and hoping you can help. My issue is the same as Puneet's in which I do want to identify the overlap based on a criteria but now I want to know what is that min date and the max date. Any tricks up your sleeve?

Thank you!

Liz,

read this post:

Calculate min and max date among overlapping date ranges and based on a condition

Hi Oscar,

Thank you for your post on the min and max. I've been using your formulas but I don't think it is working for the set of values that I'm working with since my dates include time.I have over 12000 records in my list and here is a snippet. Need to calculate the non-overlap hours per Identifier. How can I get to this?

Start End Identifier Non-Overlap Hours per Identifier

11/1/2012 0:00 11/1/2012 10:30 115854 10.5

11/1/2012 0:00 11/1/2012 10:30 117534 10.5

11/1/2012 1:00 11/1/2012 17:00 115854 6.5

11/1/2012 5:00 11/1/2012 15:00 117534 4.5

11/1/2012 10:00 11/1/2012 18:00 118929 8

11/1/2012 10:00 11/1/2012 18:00 118929 0

11/1/2012 14:00 11/1/2012 17:00 113569 3

11/1/2012 14:00 11/1/2012 17:00 113569 0

Using your formula I added an If statement so that if there is an overlap put a 0 and then put a 1 when there is no overlap but I do not think that addresses what I need to solve. Any suggestions are greatly appreciated.

Liz,

I am not following, isn´t the Non-Overlap Hours per Identifier 1 hour, in this example?

11/1/2012 0:00 11/1/2012 10:30 115854 10.5 <-- 1 hour and 11/1/2012 1:00 11/1/2012 17:00 115854 6.5

Hi Oscar,

I see what you mean now that the first one will be 1 hour. I need to calculate the total non-overlap hours per identifier.

I appreciate your help!

[…] Liz asks: […]

Hello,

I am having a problem figuring out the below.

I have multiple identifiers and I need to find out if they have overlapping dates and times? Can someone please help? Thank you kindly.

Facilitator Date Time Overlap

Susannah Cahillane Tuesday, October 7, 2014 04:00 pm-07:00 pm

Emily Ferrara Tuesday, September 30, 2014 04:00 pm-07:00 pm

Lynette Bradley Thursday, October 2, 2014 04:00 pm-07:00 pm

Renata Pienkawa Thursday, December 4, 2014 04:00 pm-06:00 pm

Dana Furbush Thursday, October 2, 2014 03:30 pm-06:30 pm

Ariel Nelson Monday, November 17, 2014 03:30 pm-06:30 pm

Carla Bruzzese Thursday, October 2, 2014 04:00 pm-07:00 pm

Elizabeth Gospodarek Thursday, October 2, 2014 04:00 pm-07:00 pm

Sunita Mehrotra Monday, September 22, 2014 04:00 pm-07:00 pm

Kellie Jones Thursday, October 9, 2014 04:30 pm-07:30 pm

Pavlina Gatikova Tuesday, October 7, 2014 04:00 pm-06:00 pm

Gail Arsenault Monday, October 6, 2014 03:45 pm-06:45 pm

Sunita Mehrotra Wednesday, October 1, 2014 04:30 pm-07:30 pm

Sunita Mehrotra Tuesday, October 7, 2014 04:30 pm-07:30 pm

Christine Nicholson Wednesday, October 1, 2014 04:30 pm-06:30 pm

Allison Levit Monday, October 27, 2014 03:30 pm-06:30 pm

Peter Dillon Wednesday, October 1, 2014 04:00 pm-07:00 pm