# 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 you need to use a more complicated array formula.

#### What's on this webpage

## 1. Count overlapping days for all date ranges

I have three date ranges (B3:C5) in this example and I want to count the number of days that overlap another date range (B9:C9).

This array formula counts overlapping days for each date range in cell range B3:C5 compared to the date range in cell range B9:C9.

Array formula in cell S3:S5:

Excel 365 formula in cell R2:

The Excel 365 formula above is a dynamic array formula and works only in Excel 365.

Both formulas above return this array: {**3**; **4**; **1**}, the numbers correspond to the date ranges in rows 2,3, and 4.

Date range 2005-01-03/2005-01-05 (A2:B2) has **3** overlapping dates compared to 2005-01-02/2005-01-12 (A8:B8).

Date range 2005-01-07/2005-01-10 (A3:B3) has **4** overlapping dates compared to 2005-01-02/2005-01-12 (A8:B8).

Date range 2005-01-12/2005-01-13 (A3:B3) has **1** overlapping date compared to 2005-01-02/2005-01-12 (A8:B8).

### Explaining formula in cell S3:S5

#### Step 1 - Count days in date range

$C$9-$B$9+1

becomes

38364-38354+1

and returns 11.

#### Step 2 - Create a cell reference

The INDEX function is also able to create a cell reference.

INDEX(**$B:$B**, $C$9-$B$9+1)

becomes

INDEX(**$B:$B**, 11)

and returns A11.

#### Step 3 - Concatenate cell references

A1:INDEX($A:$A, $C$9-$B$9+1)

returns A1:A11.

#### Step 4 - Calculate row numbers based on cell reference

The ROW function returns a row number from a cell reference. It returns multiple row numbers if the cell reference points to a cell range.

ROW(A1:INDEX($A:$A, $C$9-$B$9+1))

becomes

ROW(A1:A11)-1

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}

#### Step 5 - Subtract with 1

ROW(A1:INDEX($A:$A, $C$9-$B$9+1))-1

becomes

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11} - 1

and returns

{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

#### Step 6 - Add array to start date

$B$9+ROW(A1:INDEX($A:$A, $C$9-$B$9+1))-1

becomes

38354+{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

and returns {38354; 38355; 38356; 38357; 38358; 38359; 38360; 38361; 38362; 38363; 38364}

#### Step 7 - Convert vertical array to a horizontal array

The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa.

TRANSPOSE(array)

TRANSPOSE($B$9+ROW(A1:INDEX($A:$A, $C$9-$B$9+1))-1)

becomes

TRANSPOSE({38354; 38355; 38356; 38357; 38358; 38359; 38360; 38361; 38362; 38363; 38364})

and returns

{38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364}

#### Step 8 - Test which dates are larger or equal to start dates

TRANSPOSE($B$9+ROW(A1:INDEX($A:$A,$C$9-$B$9+1))-1)>=$B$3:$B$5

becomes

{38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364}>=$B$3:$B$5

becomes

{38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364}>={38355; 38359; 38361}

and returns

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

#### Step 9 - Test which dates are smaller or equal to end dates

TRANSPOSE($B$9+ROW(A1:INDEX($A:$A,$C$9-$B$9+1))-1)<=C3:C5

becomes

{38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364}<=C3:C5

becomes

{38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364}<={38357;38362;38365}

and returns

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

#### Step 10 - Multiply arrays

This step identifies dates inside a date range, both arrays must return TRUE to return TRUE which is the same as AND-logic.

(TRANSPOSE($B$9+ROW(A1:INDEX($A:$A,$C$9-$B$9+1))-1)>=$B$3:$B$5)*(TRANSPOSE($B$9+ROW(A1:INDEX($A:$A,$C$9-$B$9+1))-1)<=C3:C5)

becomes

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

and returns

{0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0;0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1}.

Note that 1 is equal to the boolean value TRUE and 0 (zero) is FALSE.

#### Step 11 - Create an array of numbers all equal to 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. This makes it easy to create an array of numbers all equal to 1.

ROW(A1:INDEX($A:$A,$C$9-$B$9+1))^0

becomes

{1;2;3;4;5;6;7;8;9;10;11}^0

and returns

{1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1}

#### Step 12 - 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((TRANSPOSE($B$9+ROW(A1:INDEX($A:$A,$C$9-$B$9+1))-1)>=$B$3:$B$5)*(TRANSPOSE($B$9+ROW(A1:INDEX($A:$A,$C$9-$B$9+1))-1)<=C3:C5),ROW(A1:INDEX($A:$A,$C$9-$B$9+1))^0)

becomes

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

and returns {3;4;4}.

## 2. Count all overlapping days

To count all overlapping days, array formula in cell B12:

Functions in array formulas: MMULT, ROW, INDEX

### Overlapping date ranges in cell range A2:B4

Keep in mind that if you have overlapping date ranges in A2:B4, overlapping dates will be counted twice or more.

In this example, date 9 and 10 are overlapped by 2005-01-07/2005-01-10 and 2005-01-09/2005-01-13. They are counted twice, see values above in cell range S4:S5.

The value in cell A11 is wrong because of this, only 9 dates are overlapped. There is a formula for this scenario also but I'll save it for a future post.

Join my Advanced excel course and learn more.

### Count values category

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

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

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]

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

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

### Overlapping category

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

This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]

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

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

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 explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

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

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

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

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

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 8 Responses to “Count overlapping days in multiple 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

Hi admin, i see that https://www.get-digital-help.com needs fresh posts. Daily updates will rank your page in google higher, content is king nowadays. If you are to lazy to write unique posts everyday you should search in google for:

Ightsero's Essential Tool

Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, play with it, and come to grips with its basics. Your example was clear and very useful, and this has allowed me to do big, very useful data analysis for the company that employs me. It applies to thousands of people. You're an unsung hero.

Actually just realized that I really need what you said you'd "save for a future post" (actually total number of overlapping dates). I guess I'll try to figure that out. Still... couldn't have gotten this close without you.

NC,

read this post:

https://www.get-digital-help.com/2015/08/12/count-overlapping-days-across-multiple-date-ranges/

[…] NC asks: […]

in the above eg. if now i have multiple date range ( ie.(A8:B8) is now (A8:B20) or more and the range (A2:B4 ) is now (A2: B100) , then how to calculate overlap for each row (A8:B8), (A9:B9) and so on

Also this needs to account for workdays only, and the data in (A8:B8)... are dynamic

Thank you so much for all your posts.

am trying to count number of unique overlap days between a start and end date and a range of start and end dates, it is I think what you referred to as a future post - but that calculates all the overlapped dates, am only interested in one set of overlapped dates.

is that a relatively easy ask? hope so.

Thanks - sfd