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

*Article updated on July 27, 2017*

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

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 […]### 5 Responses to “Count overlapping days in multiple date ranges, part 2”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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?