How to get the number of months that overlap between two sets of dates

microsoft excelworksheet-function

Range1 is 6/1/2021-11/30/2021 and Range2 is 8/15/2021-3/1/2022 (Range2 could be any other range of dates). I want to see how many months of Range2 intersect with Range1. The answer should be 4 because Nov, Oct, Sep and part of Aug intersect Range2. And in my problem even if it was 3 months and 3 days I would say 4 full months. However, I am not counting the first day of the month as a full month.

For example, Range1 is 6/1/2021 – 11/1/2021 and Range2 is 2/1/2021 – 10/1/2021, then the answer should be 4 because June, July, August, and September intersect with Rang1. Note that I am not counting 10/1/2021 so the answer could not be 5.

Best Answer

If one has Office 365 Excel we can use FILTER to return the months that coincide. And Unique to return the unique list. Then count to count the number of months:

=COUNT(UNIQUE(FILTER(MONTH(SEQUENCE(B1-A1,,A1)),ISNUMBER(MATCH(SEQUENCE(B1-A1,,A1),SEQUENCE(B2-A2,,A2),0)),"")))

enter image description here

The SEQUENCE returns arrays that start at the first day and end at the day before the end date. Then we use MATCH to see if that date is in the second list of dates. If so we return the month of the first date.

The the UNIQUE will only return the month numbers which are in both lists and COUNT will count them.

Related Question