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 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
Count overlapping days in multiple date ranges.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges 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 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 […]
Extract unique distinct values based on the 4 last characters
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
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 Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]
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 […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
Sort based on frequency row-wise
In this article I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The second example is a […]
The POWER function calculates a number raised to a power. Excel Function Syntax POWER(number, power) Arguments number Required. The number […]
How to create a list of random unique numbers
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
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 […]
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
7 Responses to “Count overlapping days in multiple date ranges”
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
Paste image link to your comment.
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