Count overlapping months from date ranges

[jasonnj1978pwest]

Hi everyone,

Longtime browser of site and this is my first post!!

So I've been struggling with finding a formula that will help me count how many months are overlapping between more than 2 date ranges. Below is a sample of that data I am working with.


I was able to find a formula that would tell me what date spans were overlapping:
Cell H2: =IF(SUMPRODUCT(--(A:A=A2)*--(F:F<G2)*--(G:G>F2))>1,"Overlapping","")

I thought I could use that to then use a formula that would evaluate all the dates and tell me how many months of overlap there are. In this example I would expect 9 months of overlap.

If anyone can help with this I'd greatly appreciate it!!

 

[jasonnj1978pwest]

2 rows of overlap.

Rows 5 and 6 overlap with row 7.

Row 5 has 2 months that overlap with row 7
Row 6 has 7 months that overlap with row 7

 

[jasonnj1978pwest]

Duplicate post, sorry.

 

[aaronbens]

One last question, if the end date on line 5 is March 31, does that change your overlap to 10?

 

[jasonnj1978pwest]

I see where your coming from with that question, thankfully that is scenario that cannot happen because the eligibility system that we use would not allow it. To be specific it will not allow the entry of an overlapping span belonging to the same ID_1.

The whole issue here that I'm trying to gather info from stems from 1 person being assigned different ID numbers which is not supposed to happen, but it is, so I've been tasked to try and find out what kind of impact this is having. My concern is with people that have overlapping eligibility spans as they will cause the most problems for us.

Thank you

 

[Marcelo Branco]

Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

PRIMARY_ID​

[/td][td]

ID_1​

[/td][td]

ID_2​

[/td][td]

FIRSTNAME​

[/td][td]

LASTNAME​

[/td][td]

EFF_DATE​

[/td][td]

END_DATE​

[/td][td]

Ov. Months​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1234567​

[/td][td]

76543210​

[/td][td]

1234567​

[/td][td]

JANE​

[/td][td]

DOE​

[/td][td]

11/01/2013​

[/td][td]

11/30/2013​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1234567​

[/td][td]

1234567​

[/td][td]

76543210​

[/td][td]

JANE​

[/td][td]

DOE​

[/td][td]

09/01/2014​

[/td][td]

03/31/2015​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1234567​

[/td][td]

76543210​

[/td][td]

1234567​

[/td][td]

JANE​

[/td][td]

DOE​

[/td][td]

03/01/2016​

[/td][td]

04/30/2016​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

1234567​

[/td][td]

1234567​

[/td][td]

76543210​

[/td][td]

JANE​

[/td][td]

DOE​

[/td][td]

01/01/2017​

[/td][td]

02/28/2017​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

1234567​

[/td][td]

1234567​

[/td][td]

76543210​

[/td][td]

JANE​

[/td][td]

DOE​

[/td][td]

03/01/2017​

[/td][td]

09/30/2017​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

1234567​

[/td][td]

76543210​

[/td][td]

1234567​

[/td][td]

JANE​

[/td][td]

DOE​

[/td][td]

05/01/2016​

[/td][td]

10/31/2017​

[/td][td]

0​

[/td][/tr]
[/table]

Array formula in H2 copied down
=SUM(IF(A$2:A$7=A2,IF(ROW(G$2:G$7)>ROW(G2),IF(1-((F$2:F$7>=G2)+(F2>=G$2:G$7)),1+IF(G$2:G$7>G2,12*YEAR(G2)+MONTH(G2),12*YEAR(G$2:G$7)+MONTH(G$2:G$7))-IF(F$2:F$7>F2,12*YEAR(F$2:F$7)+MONTH(F$2:F$7),12*YEAR(F2)+MONTH(F2))))))
Ctrl+Shift+Enter

Hope this helps

M.

 

[jasonnj1978pwest]

SOLVED Count overlapping months from date ranges

Maybe this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

PRIMARY_ID​

[/TD]
[TD]

ID_1​

[/TD]
[TD]

ID_2​

[/TD]
[TD]

FIRSTNAME​

[/TD]
[TD]

LASTNAME​

[/TD]
[TD]

EFF_DATE​

[/TD]
[TD]

END_DATE​

[/TD]
[TD]

Ov. Months​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1234567​

[/TD]
[TD]

76543210​

[/TD]
[TD]

1234567​

[/TD]
[TD]

JANE​

[/TD]
[TD]

DOE​

[/TD]
[TD]

11/01/2013​

[/TD]
[TD]

11/30/2013​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1234567​

[/TD]
[TD]

1234567​

[/TD]
[TD]

76543210​

[/TD]
[TD]

JANE​

[/TD]
[TD]

DOE​

[/TD]
[TD]

09/01/2014​

[/TD]
[TD]

03/31/2015​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1234567​

[/TD]
[TD]

76543210​

[/TD]
[TD]

1234567​

[/TD]
[TD]

JANE​

[/TD]
[TD]

DOE​

[/TD]
[TD]

03/01/2016​

[/TD]
[TD]

04/30/2016​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1234567​

[/TD]
[TD]

1234567​

[/TD]
[TD]

76543210​

[/TD]
[TD]

JANE​

[/TD]
[TD]

DOE​

[/TD]
[TD]

01/01/2017​

[/TD]
[TD]

02/28/2017​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

1234567​

[/TD]
[TD]

1234567​

[/TD]
[TD]

76543210​

[/TD]
[TD]

JANE​

[/TD]
[TD]

DOE​

[/TD]
[TD]

03/01/2017​

[/TD]
[TD]

09/30/2017​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

1234567​

[/TD]
[TD]

76543210​

[/TD]
[TD]

1234567​

[/TD]
[TD]

JANE​

[/TD]
[TD]

DOE​

[/TD]
[TD]

05/01/2016​

[/TD]
[TD]

10/31/2017​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in H2 copied down
=SUM(IF(A$2:A$7=A2,IF(ROW(G$2:G$7)>ROW(G2),IF(1-((F$2:F$7>=G2)+(F2>=G$2:G$7)),1+IF(G$2:G$7>G2,12*YEAR(G2)+MONTH(G2),12*YEAR(G$2:G$7)+MONTH(G$2:G$7))-IF(F$2:F$7>F2,12*YEAR(F$2:F$7)+MONTH(F$2:F$7),12*YEAR(F2)+MONTH(F2))))))
Ctrl+Shift+Enter

Hope this helps

M.

This worked great, thank you so much!!

 

[Marcelo Branco]

Re: SOLVED Count overlapping months from date ranges

You are welcome. Glad to help.

M.

 

[craftsman729]

Re: SOLVED Count overlapping months from date ranges

I have a similiar problem. I am trying to see the overlap of a row of data when comparing the the rest of the rows of data. Here is my data field:

[TABLE="class: grid, width: 40, align: center"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Duration (months)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/1/2014[/TD]
[TD]12/31/2015[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6/1/2015[/TD]
[TD]1/31/2018[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/1/2015[/TD]
[TD]6/30/2016[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/1/2016[/TD]
[TD]9/1/2018[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4/1/2016[/TD]
[TD]12/31/2017[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/1/2018[/TD]
[TD]8/31/2018[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2/1/2018[/TD]
[TD]12/31/2019[/TD]
[TD]23[/TD]
[/TR]
</tbody>[/TABLE]

For example, Project 1 would be compared to Projects 2 through 7 for an overlap of 7 months (6/1/2015 to 12/31/2015); Project 2 would be compared to Project 1 and Projects 3 through 7 for an overlap of 32 months; and so on. The start dates are in sequential order from oldest to newest. The end dates fluctuate based on the duration of the project. How would I calculate each project's overlap with the rest of the projects?

Also, I need to determine the total duration of all projects. The data set above is pretty straight forward, but if there is a gap in the data, it would need to deduct this from the overall duration.

Your help would be very appreciated.