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 ranges. The MMULT function is a great excel function, it allows you to do really amazing calculations with date ranges. Yes, I have said that before.
For simplicity, in this example there are only 4 date ranges in column B and C and you can see their length in the chart to the right.
Let's begin with a simple formula, it will be helpful for you if you understand this one. It calculates the number of date ranges that overlaps the first date range:
It returns 3 overlapping date ranges, which is correct. Range 2, 3 and 4 overlap range 1.
Read this post if you want to know more about this formula:
Find overlapping date ranges.
Calculate all ranges
How do we calculate the number of overlapped ranges for all ranges in one formula?
This array formula returns an array: {3;2;2;1}. The first date range has 3 overlapping ranges, described above. The second has 2, range 1 and 3 overlaps range 2. The third has 2 overlapping ranges, range 1 and 2. The fourth range has 1 overlapping date range, range 1.
In the animated picture below the drop down list in cell D7 allows you to select a date range, cell D8 tells you how many overlapping date ranges the selected date range has.
I applied conditional formatting to easily spot the selected date range.
Array formula in cell D8:
Find most overlapped date range
Using this technique we can now construct a formula that finds the most overlapped date range, array formula in cell B11:
Here is the formula for the least overlapped date range, cell B14:
Want to know more about the excel functions used in the formulas above?
SUMPRODUCT, MMULT, INDEX, MATCH
Interested in learning more about excel, join my Advanced excel course.
Download excel *.xlsx file
Working with overlapping date ranges.xlsx
Related articles
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 overlapping days across multiple date ranges
NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, [โฆ]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to [โฆ]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the [โฆ]
Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there [โฆ]
The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and [โฆ]
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 [โฆ]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in [โฆ]
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 [โฆ]
One Response to โWorking with overlapping 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.
English
Spanish
Hey! Awesome article. Instead of finding the most overlapped date, how would you find the largest number of overlaps (i.e. count of the overlaps on the date instead of the date)?