# 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.

#### What's on this webpage

## 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.

## 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.

### 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}.

## 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:

## 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

## 4. Least overlapped date range

Array formula in cell B14:

## 5. Most overlapped date

Array formula in cell C18:

## 6. Least overlapped date

Array formula in cell C21:

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

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

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

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]

This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]

This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]

The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

### One Response to “Working with overlapping date ranges”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hey! Awesome article. Instead of finding the most overlapped date, how would you find the largest number of overlaps (i.e. count of the overlaps on the date instead of the date)?