Count the number of date ranges that overlap other date ranges

[maverick8519]

Hi all,

I am trying to figure out a way in excel to count the number of date range that overlap other date ranges. For example my data is set out like this

Subject Start End Outcome
A 9/05/09 20/05/09 2
B 10/05/09 25/05/09 2
C 1/06/09 15/06/09 3
D 3/06/09 20/06/09 3
E 10/06/09 25/06/09 3

I have tried using Sumproduct and countif function but all failed.

I want to have excel to count the overlap dates to the answer in outcome column. So it will count using subject A's first start and end dates as 1 then if there is another date range that overlaps another date range which in this caise Subject B it will count that as the 2nd one. Therefore both Subject A and B count will equal to 2. However if the first date range does not overlap any other date ranges it will count that as only 1.

Anyone able to help, I hope this make sense :P


Cheers,

 

[shemayisroel]

error - disreguardhttp://www.excel-jeanie-html.de/index.php?f=1

 

[maverick8519]

thanks here you go

Excel Workbook
	A	B	C	D
1	ESN	Start	End	Expect Answer
2	13185	09-May-09	23-Jun-09	1
3	13147	16-Jul-09	30-Aug-09	1
4	13297	15-Sep-09	30-Oct-09	1
5	13286	10-Nov-09	25-Dec-09	2
6	13295	30-Nov-09	14-Jan-10	2
7	13132	20-Jan-10	06-Mar-10	3
8	13148	03-Feb-10	20-Mar-10	3
9	13131	08-Feb-10	25-Mar-10	3
10	13275	10-Mar-10	24-Apr-10	2
11	13157	30-Mar-10	14-May-10	2
Sheet2

 

[Peter_SSs]

maverick8519

Welcome to the MrExcel board!


See if this formula (copied down) does what you want.

Excel Workbook
	A	B	C	D	E
1	Subject	Start	End	Outcome
2	A	09/05/2009	20/05/2009	2
3	B	10/05/2009	25/05/2009	2
4	C	01/06/2009	15/06/2009	3
5	D	03/06/2009	20/06/2009	3
6	E	10/06/2009	25/06/2009	3
7
Overlap

 

[Peter_SSs]

maverick8519

You posted your second sample while I was posting my reply above so I had not seen this data. My formula gives slightly different results. Have I misunderstood the concept or were your expected results wrong in a couple of the rows.

For example, for the row highlighted blue in columns D:E I have found 4 date ranges that overlap, not 3. Is that correct?

Excel Workbook
	A	B	C	D	E
1	ESN	Start	End	Peter_SSs	maverick8519
2	13185	09-May-09	23-Jun-09	1	1
3	13147	16-Jul-09	30-Aug-09	1	1
4	13297	15-Sep-09	30-Oct-09	1	1
5	13286	10-Nov-09	25-Dec-09	2	2
6	13295	30-Nov-09	14-Jan-10	2	2
7	13132	20-Jan-10	06-Mar-10	3	3
8	13148	03-Feb-10	20-Mar-10	4	3
9	13131	08-Feb-10	25-Mar-10	4	3
10	13275	10-Mar-10	24-Apr-10	4	2
11	13157	30-Mar-10	14-May-10	2	2
12
Overlap

 

[maverick8519]

Thanks Peter, that was my first formula but it didn't quite work out, here is my complete data and here is a link to the chart that I have attached to give some visual perspective.

http://img412.imageshack.us/img412/3411/jeanie.gif

From the image you can see the overlap count shouldn't be greater than 3 since there are only 3 dates thats overlap or the same.


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>ESN</TD><TD>Start</TD><TD>End</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">13185</TD><TD style="TEXT-ALIGN: right">09-May-09</TD><TD style="TEXT-ALIGN: right">23-Jun-09</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">13147</TD><TD style="TEXT-ALIGN: right">16-Jul-09</TD><TD style="TEXT-ALIGN: right">30-Aug-09</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">13297</TD><TD style="TEXT-ALIGN: right">15-Sep-09</TD><TD style="TEXT-ALIGN: right">30-Oct-09</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">13286</TD><TD style="TEXT-ALIGN: right">10-Nov-09</TD><TD style="TEXT-ALIGN: right">25-Dec-09</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">13295</TD><TD style="TEXT-ALIGN: right">30-Nov-09</TD><TD style="TEXT-ALIGN: right">14-Jan-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">13132</TD><TD style="TEXT-ALIGN: right">20-Jan-10</TD><TD style="TEXT-ALIGN: right">06-Mar-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">13148</TD><TD style="TEXT-ALIGN: right">03-Feb-10</TD><TD style="TEXT-ALIGN: right">20-Mar-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">13131</TD><TD style="TEXT-ALIGN: right">08-Feb-10</TD><TD style="TEXT-ALIGN: right">25-Mar-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">13275</TD><TD style="TEXT-ALIGN: right">10-Mar-10</TD><TD style="TEXT-ALIGN: right">24-Apr-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">13157</TD><TD style="TEXT-ALIGN: right">30-Mar-10</TD><TD style="TEXT-ALIGN: right">14-May-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">13288</TD><TD style="TEXT-ALIGN: right">12-May-10</TD><TD style="TEXT-ALIGN: right">26-Jun-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">13291</TD><TD style="TEXT-ALIGN: right">25-May-10</TD><TD style="TEXT-ALIGN: right">09-Jul-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">13276</TD><TD style="TEXT-ALIGN: right">12-Jun-10</TD><TD style="TEXT-ALIGN: right">27-Jul-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">13250</TD><TD style="TEXT-ALIGN: right">06-Jul-10</TD><TD style="TEXT-ALIGN: right">20-Aug-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">13249</TD><TD style="TEXT-ALIGN: right">07-Aug-10</TD><TD style="TEXT-ALIGN: right">21-Sep-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">13287</TD><TD style="TEXT-ALIGN: right">07-Aug-10</TD><TD style="TEXT-ALIGN: right">21-Sep-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">13158</TD><TD style="TEXT-ALIGN: right">13-Aug-10</TD><TD style="TEXT-ALIGN: right">27-Sep-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">13187</TD><TD style="TEXT-ALIGN: right">29-Aug-10</TD><TD style="TEXT-ALIGN: right">13-Oct-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">13188</TD><TD style="TEXT-ALIGN: right">28-Sep-10</TD><TD style="TEXT-ALIGN: right">12-Nov-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">13290</TD><TD style="TEXT-ALIGN: right">21-Oct-10</TD><TD style="TEXT-ALIGN: right">05-Dec-10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">13453</TD><TD style="TEXT-ALIGN: right">29-Nov-10</TD><TD style="TEXT-ALIGN: right">13-Jan-11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">13294</TD><TD style="TEXT-ALIGN: right">28-Dec-10</TD><TD style="TEXT-ALIGN: right">11-Feb-11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">13289</TD><TD style="TEXT-ALIGN: right">04-Feb-11</TD><TD style="TEXT-ALIGN: right">21-Mar-11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">13129</TD><TD style="TEXT-ALIGN: right">04-Feb-11</TD><TD style="TEXT-ALIGN: right">21-Mar-11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">13130</TD><TD style="TEXT-ALIGN: right">04-Feb-11</TD><TD style="TEXT-ALIGN: right">21-Mar-11</TD></TR></TBODY></TABLE>

 

[maverick8519]

Sorry I mean shouldn't be greater than 4 just release ESN 13287, 13249, 13158 & 13187 dates overlap

 

[Peter_SSs]

From the image you can see the overlap count shouldn't be greater than 3 since there are only 3 dates thats overlap or the same.

I don't understand. With this latest data, if I choose row 8 (13148 03-Feb-10 20-Mar-10) for example. My formula gives a result of 4. When I look at your chart image and locate the 13148 bar, I can then see 1 bar below and 2 two bars above that at least partially overlap the 13148 bar. Counting the bar itself (as I think you want to do) that makes 4.

So, where am I going wrong?


Edit: Sorry, again missed your last post. I'll have another look.

 

[Peter_SSs]

OK, same sort of argument as my last post. Look at the 4th last bar (near the top right of your chart). I can see 3 bars above it and 1 below that overlap it. Counting the bar itself, that makes 5 (which the formula gives)

Note that there is also a 6 (using the 13250 bar as the 'base' bar - 3 above and 2 below)

 

[smhall1205]

What if I have a set start and end date, and then a large range of date ranges that I want to check against, for example;

[TABLE="width: 334"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Date Out[/TD]
[TD]14/06/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date In[/TD]
[TD]20/06/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]date out[/TD]
[TD]date in[/TD]
[/TR]
[TR]
[TD]LTS1[/TD]
[TD]22/05/2018[/TD]
[TD]23/05/2018[/TD]
[/TR]
[TR]
[TD]TKC25285[/TD]
[TD]15/06/2018[/TD]
[TD]22/06/2018[/TD]
[/TR]
[TR]
[TD]TKC27482[/TD]
[TD]15/06/2018[/TD]
[TD]22/06/2018[/TD]
[/TR]
[TR]
[TD]TKC31446[/TD]
[TD]15/06/2018[/TD]
[TD]22/06/2018[/TD]
[/TR]
[TR]
[TD]TIL12403[/TD]
[TD]19/09/2018[/TD]
[TD]20/09/2018[/TD]
[/TR]
[TR]
[TD]TIL12960[/TD]
[TD]19/09/2018[/TD]
[TD]20/09/2018[/TD]
[/TR]
</tbody>[/TABLE]

Essentially, if the start and end date at the top overlap with any of the date ranges below, I need a formula that will say overlap or not overlap