## Count overlapping days in multiple date ranges

*Article updated on February 20, 2018*

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.

I have three date ranges (A2:B4) in this example and I want to count the number of days that overlap another date range (A8:B8).

This array formula counts overlapping days for each date range in cell range A2:B4 compared to date range in cell range A8:B8.

**Array formula in cell R2:R4:**

It returns this array: {**3**; **4**; **1**}. 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).

To count all overlapping days, **array formula in cell A11:**

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 cell range R3:R4.

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.

### Download example *.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 […]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 […]### 7 Responses to “Count overlapping days in multiple date ranges”

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