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

 

[NARAYANK991]

Hi ,

When uploading a sample workbook , try to include data that will represent all possible situations.

Can you go through the attached workbook and indicate what will be the number of overlapping months ?

Narayan

 

[jasonnj1978pwest]

Thank you for your reply NARAYANK991.

This sample represents all known situations that I have in my dataset.

The expected result from this sample is 9 months of overlap. Rows 5 and 6 overlap with row 7. To break it down, row 5 has 2 months of overlap with row 7 and row 6 has 7 months of overlap with row 7.

 

[NARAYANK991]

Hi ,

With more data , we can get a better idea of your data layout ; as it stands , only one member's data is shown ; with just this , it is difficult to see how and where helper columns can be used.

See if the attached file helps.

Narayan

 

[jasonnj1978pwest]

Here is a file with some more data.

 

[NARAYANK991]

Hi ,

See the revised file.

Narayan