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
Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]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 […]One Response to “Working with overlapping 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.
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)?