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

 

[aaronbens]

How do you want to handle partial months? if the overlapping time is from March 20th to June 10th, do you want to see 4 months overlap to include each partial month as a full month or do you want to see 2 for only full partial months? Are you only evaluating 2 date ranges?

 

[jasonnj1978pwest]

Thank you for the reply aaronbens.

Here are the answers to your questions:

1. If there is 1 day or greater overlap then round up to consider it 1 month. I'd want to see 4 months in your example.
2. There are 2 date ranges, an eff_date column and and end_date column. There are several rows of date spans for each unique person_id in my data and the data is sorted by that unique person_id and then by the end_date in ascending order i.e., oldest end date to newest end date.

I tried to post a picture of a small data set example and to attach an excel file of that but I don't have permissions for attachements.

 

[aaronbens]

So unique person#1 could have 5 or 10 date ranges listed? If so, you want the sum of the overlap of all the ranges?

 

[Marcelo Branco]

I tried to post a picture of a small data set example and to attach an excel file of that but I don't have permissions for attachements.

Try to post a data sample along with expected results
See section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[jasonnj1978pwest]

Yes, sum of overlap of all the ranges.

 

[jasonnj1978pwest]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]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]OVERLAPPING[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01234567[/TD]
[TD]76543210[/TD]
[TD]01234567[/TD]
[TD]JANE[/TD]
[TD]DOE[/TD]
[TD]11/1/2013[/TD]
[TD]11/30/2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01234567[/TD]
[TD]01234567[/TD]
[TD]76543210[/TD]
[TD]JANE[/TD]
[TD]DOE[/TD]
[TD]9/1/2014[/TD]
[TD]3/31/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01234567[/TD]
[TD]76543210[/TD]
[TD]01234567[/TD]
[TD]JANE[/TD]
[TD]DOE[/TD]
[TD]3/1/2016[/TD]
[TD]4/30/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01234567[/TD]
[TD]01234567[/TD]
[TD]76543210[/TD]
[TD]JANE[/TD]
[TD]DOE[/TD]
[TD]1/1/2017[/TD]
[TD]2/28/2017[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]01234567[/TD]
[TD]01234567[/TD]
[TD]76543210[/TD]
[TD]JANE[/TD]
[TD]DOE[/TD]
[TD]3/1/2017[/TD]
[TD]9/30/2017[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]01234567[/TD]
[TD]76543210[/TD]
[TD]01234567[/TD]
[TD]JANE[/TD]
[TD]DOE[/TD]
[TD]5/1/2016[/TD]
[TD]10/31/2017[/TD]
[TD]YES[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 84px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]01234567[/TD]
[/TR]
</tbody>[/TABLE]
</body>












Sample data of what I'm working with.

 

[jasonnj1978pwest]

I would expect 9 months overlap in my example.

 

[aaronbens]

Sorry for all the questions. You said you would expect 9 months of overlap, but I only see the last 2 rows as having any overlap and I count 5 months (May, June, July, August, September). Row 5 ends the day before row 6 starts. Am I reading it wrong?

 

[63falcondude]

Cross-posted here: https://www.excelforum.com/excel-fo...ount-overlapping-months-from-date-ranges.html