Count overlapping months from date ranges

**jasonnj1978pwest** · 10-18-2017, 11:27 AM

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.

Overlapping screenshot.JPG

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

**davsth** · 10-18-2017, 11:42 AM

An attachment would be easier to work with, especially if you explain your calculations for what is your expected result is.

but a few questions
what is your definition of months of overlap?
if 1 day is an overlap, does it count as a full month?
if 3 rows overlap do you only count the months when any of them overlap?
will the rows always be sorted by mbrprimary id and then ascending dt_ymdeff?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**jasonnj1978pwest** · 10-18-2017, 02:17 PM

Thanks for the reply davsth.

To answer your questions:
1. Yes, 1 day of overlap would count as a full month
2. Yes, only count months of the rows that overlap whether it's 1 row or 10 rows.
3. The rows will be sorted by primary_id and then by dt_ymdend ascending (oldest new newest)