• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count overlapping months from date ranges

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

Attachments

  • Overlapping1.xlsx
    9.1 KB · Views: 8
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
 

Attachments

  • Book 1.xlsx
    9.2 KB · Views: 2
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.
 
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
 

Attachments

  • Sample File.xlsx
    13.3 KB · Views: 2
Back
Top