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:


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?


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:


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:


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


Want to know more about the excel functions used in the formulas above?

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

Download excel *.xlsx file

Working with overlapping date ranges.xlsx