## Count overlapping days across multiple date ranges

NC asks:

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

Answer:

Thanks NC, here comes that "future" post. This post demonstrates how to count overlapping days except duplicates across multiple date ranges.

I began working with this formula in cell range R2:R4:

This formula returns a number indicating overlapping days, for each date range. There are 3 date ranges so the array contains 3 values.

### Modified formula

What I was looking for was a formula that counts overlapping days, for each date instead of each date range. The date range is 2005-01-02 / 2005-01-12, 11 days and therefore 11 values in the returning array. This is what I came up with, entered in cell range E6:O6:

You can´t sum the values in this array, you will get 11, counting duplicates that I don´t want. (0+1+1+1+0+1+1+2+2+1+1 = 11).

### Convert values in array

This array formula, entered in E8:O8, converts zeros to #num error and values above 0 to 1.

returns

{#NUM!, 1, 1, 1, #NUM!, 1, 1, 1, 1, 1, 1}

### Count number of values in array that contain numbers

returns 9.

This array formula is entered in cell A11.

### Download excel *.xlsx file

### Category: Mmult function

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

Filter rows where a cell contains a numeric value

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]Comments(10) Filed in category: Excel, MMULT function

Comments(7) Filed in category: Count values, Excel, MMULT function, SUM function

Lookup with multiple criteria and display multiple unique search results (array formula)

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]Comments(5) Filed in category: Excel, MMULT function, Search and return multiple values, Unique distinct values

MMULT function – Matrix multiplication

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]Comments(4) Filed in category: Excel, MMULT function

### Category: Overlapping

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Comments(31) Filed in category: Excel, Overlapping

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]Comments(24) Filed in category: Excel, Overlapping

Highlight duplicate values and overlapping dates in excel

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]Comments(23) Filed in category: Excel, Overlapping

Visualize date ranges in a calendar

Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible […]Comments(10) Filed in category: Calendar, Excel, Overlapping

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]Comments(8) Filed in category: Dates, Excel, Overlapping

### One Response to “Count overlapping days across multiple date ranges”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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