Working with overlapping date ranges

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and the least overlapped range.

Today's blog post is about date ranges, the techniques demonstrated here can also be applied to Excel time values 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.

1. Calculate the number of overlapping date ranges

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.

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.

Back to top

2. Calculate the number of overlapped ranges for all ranges in one formula?

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} which corresponds to the position of each date range in row 3, 4, 5, and 6.

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.

Back to top

Explaining formula in cell I9

Step 1 - Transpose array

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

TRANSPOSE(D3:D6)

becomes

TRANSPOSE({42020; 42009; 42013; 42024})

and returns {42020, 42009, 42013, 42024}

Step 2 - Logical test 1

The less than character and the equal character combined means less than or equal to.

C3:C6<=TRANSPOSE(D3:D6)

becomes

C3:C6<={42020, 42009, 42013, 42024}

becomes

{42007; 42006; 42008; 42016}<={42020, 42009, 42013, 42024}

and returns

{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, TRUE}

Step 3 - Logical test 2

D3:D6>=TRANSPOSE(C3:C6)

becomes

{42020;42009;42013;42024}>={42007,42006,42008,42016}

and returns

{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, TRUE}.

Step 4 - Multiply arrays

When we multiply arrays we need to make sure the size of one array matches the other array. Multiplying boolean values is the same as applying AND logic. The result is the numerical equivalent TRUE = 1 and FALSE = 0 (zero).

TRUE * TRUE = 1, TRUE * FALSE = 0 (zero) and, FALSE * FALSE = 0 (zero).

(C3:C6<=TRANSPOSE(D3:D6))*(D3:D6>=TRANSPOSE(C3:C6))

becomes

{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, TRUE}*{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, TRUE}

and returns {1, 1, 1, 1; 1, 1, 1, 0; 1, 1, 1, 0; 1, 0, 0, 1}.

Step 5 - Create an array containing 1

Exponentiation is a mathematical operation, use the ^ character or the POWER function to calculate the result of exponentiation.

When a number is raised to the power of 0 (zero) the result is always 1.

C3:C6^0

becomes

{42007;42006;42008;42016}^0

and returns {1;1;1;1}.

Step 6 - Evaluate MMULT function

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(array1, array2)

MMULT((C3:C6<=TRANSPOSE(D3:D6))*(D3:D6>=TRANSPOSE(C3:C6)),C3:C6^0)

becomes

MMULT({1, 1, 1, 1; 1, 1, 1, 0; 1, 1, 1, 0; 1, 0, 0, 1}, {1;1;1;1})

and returns {4; 3; 3; 2}.

Step 7 - Subtract with 1

The MMULT function calculates all overlapping ranges even the range itself. We need to subtract with 1 to get the correct result.

MMULT((C3:C6<=TRANSPOSE(D3:D6))*(D3:D6>=TRANSPOSE(C3:C6)),C3:C6^0)-1

becomes

{4; 3; 3; 2}-1

and returns {3; 2; 2; 1}.

Back to top

2.1 Use a drop-down list to select a date range to see overlapping date ranges

In the animated picture above 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.

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

Array formula in cell D8:

Back to top

3. Find most overlapped date range

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

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

Back to top

4. Least overlapped date range

Array formula in cell B14:

Back to top

5. Most overlapped date

Array formula in cell C18:

Back to top

6. Least overlapped date

Array formula in cell C21:

Back to top

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

Get the Excel file

Working-with-overlapping-date-rangesv2.xlsx