Article updated on July 27, 2017

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

Recommended posts

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Count overlapping days across multiple date ranges

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, […]

Calculate min and max date among overlapping date ranges and based on a condition

Liz asks: I want to identify the overlap based on a criteria but now I want to know what is […]

Identify overlapping date ranges

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

Filter overlapping date ranges in excel 2007

Introduction This blog article describes how to extract coinciding date ranges. Example, Array formula Cell range B3:C25 contains example date […]