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

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 click on cell C12 […]

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

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

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

Count unique distinct records with a date and column criteria

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

How to count unique distinct items on a given date

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

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

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

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

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

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

Find missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that […]

Highlight date ranges overlapping selected record [VBA]

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional […]

Working with overlapping date ranges

Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical […]

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