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]

	A	B	C	D	E	F	G	H
1	PRIMARY_ID	ID_1	ID_2	FIRSTNAME	LASTNAME	EFF_DATE	END_DATE	OVERLAPPING
2	01234567	76543210	01234567	JANE	DOE	11/1/2013	11/30/2013
3	01234567	01234567	76543210	JANE	DOE	9/1/2014	3/31/2015
4	01234567	76543210	01234567	JANE	DOE	3/1/2016	4/30/2016
5	01234567	01234567	76543210	JANE	DOE	1/1/2017	2/28/2017	YES
6	01234567	01234567	76543210	JANE	DOE	3/1/2017	9/30/2017	YES
7	01234567	76543210	01234567	JANE	DOE	5/1/2016	10/31/2017	YES

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 84px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

01234567

<tbody>
</tbody>

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