## Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In this post I will demonstrate how to count overlapping dates among multiple date ranges.

The date ranges are in column A and B. The chart to the right is there so that you can easily verify the formula.

### Count the number of overlapping date ranges for each date

The first formula returns an array that counts the number of overlapping date ranges for each date. It is shown in cell range D12:Q12.

The formula returns this array: {1, 2, 1, 3, 1, 2, 2, 1, 0, 1, 1, 4, 3, 1}

Date 2005-01-01 is overlapped once by date range 2005-01-01/2005-01-02. So the first value in the array is 1.

Date 2005-01-02 is overlapped twice by date range 2005-01-01/2005-01-02 and 2005-01-02/2005-01-04. The second value in the array is 2.

...

Date 2005-01-09 is not overlapped at all so the ninth value in the array is 0 (zero).

And so on.., verify these numbers with the chart above.

### Count dates overlapped by two or more date ranges

The second array formula returns an array that indicates if a date is overlapped by two or more date ranges. It is entered in cell range D13:Q13.

This formula returns {0, 1, 0, 1, 0, 1, 1, 0, 0, 0, 0, 1, 1, 0}

If we sum this array we get the total number of overlapped dates. That value is shown cell D15 and R13.

Functions in this post: MMULT, ROW, INDEX

Interested in learning more about excel formulas? Join my Advanced excel course.

### Download excel *.xlsx file

Count unique distinct values that meet multiple criteria

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

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Count unique distinct values in two columns

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Count specific text string in a cell

Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

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

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

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

Identify rows of overlapping records

cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand […]

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

Highlight events overlapping federal holidays

Bryan asks: i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart) i have […]

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

### 5 Responses to “Count overlapping days in multiple date ranges, part 2”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi Oscar,

Thanks for your knowledge sharing and really helpful!

You have mentioned the overlapping date within a single month. My question is how can we highlight the overlapping dates for 2 months. Kindly find the details below for your reference.

Date Format is in (MM/DD/YYYY)

Start - 03/28/2016 - End - 04/05/2016

Start - 04/25/2016 - End - 05/10/2016

I need your help in this issue. It will be great if I get the solution for this and the same will reduce my work upto 50%. Requesting you to provide me the solution for my query.

Regards

Sam Fredy. P

Hi Oscar,

Please help. Awaiting for your positive reply.

Regards

Sam

Hi Oscar,

Pls help me for my query.

Regards

Sam

My question is probably better suited here (I had initially posted in it Part 1). For this example, I want to find the maximum number of overlaps that occurred with a set of date ranges (i.e. in this example above, it would be four (or another way of looking at it is I want the Max value of Row 12).

This is so close to what I want. However, what I would LOVE to see in column "R" is the total number of days that one date overlaps all others.

For instance, the date range on row has 2 days that overlap 3 other date ranges. I would like to see the formula for calculating that 2 days in Column "R"

Then I can do a "Countifs" for the number days that overlap with other date ranges where the same "resource" has been assigned.

Is this possible?