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

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

How to extract rows containing digits [Formula]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

Identify overlapping date ranges

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

Highlight records based on overlapping date ranges and a condition

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

How to calculate overlapping time ranges

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

Identify rows of overlapping records

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

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 […]

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