Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical ranges. The MMULT function is a great excel function, it allows you to do really amazing calculations with date ranges. Yes, I have said that before.

For simplicity, in this example there are only 4 date ranges in column B and C and you can see their length in the chart to the right.

Working with overlapping ranges

Let's begin with a simple formula, it will be helpful for you if you understand this one. It calculates the number of date ranges that overlaps the first date range:

=SUMPRODUCT((B2<=C2:C5)*(C2>=B2:B5))-1

It returns 3 overlapping date ranges, which is correct. Range 2, 3 and 4 overlap range 1.

Read this post if you want to know more about this formula:
Find overlapping date ranges.

Calculate all ranges

How do we calculate the number of overlapped ranges for all ranges in one formula?

=MMULT((B2:B5<=TRANSPOSE(C2:C5))*(C2:C5>=TRANSPOSE(B2:B5)),B2:B5^0)-1

This array formula returns an array: {3;2;2;1}. The first date range has 3 overlapping ranges, described above. The second has 2, range 1 and 3 overlaps range 2. The third has 2 overlapping ranges, range 1 and 2. The fourth range has 1 overlapping date range, range 1.

In the animated picture below the drop down list in cell D7 allows you to select a date range, cell D8 tells you how many overlapping date ranges the selected date range has.

working with overlapping ranges1

I applied conditional formatting to easily spot the selected date range.

Array formula in cell D8:

=IF(D7="","",INDEX(MMULT((B2:B5<=TRANSPOSE(C2:C5))*(C2:C5>=TRANSPOSE(B2:B5)),B2:B5^0)-1,D7))

Find most overlapped date range

Working with overlapping ranges1

Using this technique we can now construct a formula that finds the most overlapped date range, array formula in cell B11:

=MATCH(MAX(MMULT((B2:B5<=TRANSPOSE(C2:C5))*(C2:C5>=TRANSPOSE(B2:B5)),B2:B5^0)-1),MMULT((B2:B5<=TRANSPOSE(C2:C5))*(C2:C5>=TRANSPOSE(B2:B5)),B2:B5^0)-1,0)

Here is the formula for the least overlapped date range, cell B14:

=MATCH(MIN(MMULT((B2:B5<=TRANSPOSE(C2:C5))*(C2:C5>=TRANSPOSE(B2:B5)),B2:B5^0)-1),MMULT((B2:B5<=TRANSPOSE(C2:C5))*(C2:C5>=TRANSPOSE(B2:B5)),B2:B5^0)-1,0)

Want to know more about the excel functions used in the formulas above?
SUMPRODUCT, MMULT, INDEX, MATCH

Interested in learning more about excel, join my Advanced excel course.

Download excel *.xlsx file

Working with overlapping date ranges.xlsx