Working with overlapping date ranges
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and the least overlapped range.
Today's blog post is about date ranges, the techniques demonstrated here can also be applied to Excel time values 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.
What's on this webpage
1. Calculate the number of overlapping date ranges
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.
2. Calculate the number of overlapped ranges for all ranges in one formula?
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} which corresponds to the position of each date range in row 3, 4, 5, and 6.
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.
Explaining formula in cell I9
Step 1 - Transpose array
The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.
TRANSPOSE(D3:D6)
becomes
TRANSPOSE({42020; 42009; 42013; 42024})
and returns {42020, 42009, 42013, 42024}
Step 2 - Logical test 1
The less than character and the equal character combined means less than or equal to.
C3:C6<=TRANSPOSE(D3:D6)
becomes
C3:C6<={42020, 42009, 42013, 42024}
becomes
{42007; 42006; 42008; 42016}<={42020, 42009, 42013, 42024}
and returns
{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, TRUE}
Step 3 - Logical test 2
D3:D6>=TRANSPOSE(C3:C6)
becomes
{42020;42009;42013;42024}>={42007,42006,42008,42016}
and returns
{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, TRUE}.
Step 4 - Multiply arrays
When we multiply arrays we need to make sure the size of one array matches the other array. Multiplying boolean values is the same as applying AND logic. The result is the numerical equivalent TRUE = 1 and FALSE = 0 (zero).
TRUE * TRUE = 1, TRUE * FALSE = 0 (zero) and, FALSE * FALSE = 0 (zero).
(C3:C6<=TRANSPOSE(D3:D6))*(D3:D6>=TRANSPOSE(C3:C6))
becomes
{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, TRUE}*{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, FALSE; TRUE, TRUE, TRUE, TRUE}
and returns {1, 1, 1, 1; 1, 1, 1, 0; 1, 1, 1, 0; 1, 0, 0, 1}.
Step 5 - Create an array containing 1
Exponentiation is a mathematical operation, use the ^ character or the POWER function to calculate the result of exponentiation.
When a number is raised to the power of 0 (zero) the result is always 1.
C3:C6^0
becomes
{42007;42006;42008;42016}^0
and returns {1;1;1;1}.
Step 6 - Evaluate MMULT function
The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.
MMULT(array1, array2)
MMULT((C3:C6<=TRANSPOSE(D3:D6))*(D3:D6>=TRANSPOSE(C3:C6)),C3:C6^0)
becomes
MMULT({1, 1, 1, 1; 1, 1, 1, 0; 1, 1, 1, 0; 1, 0, 0, 1}, {1;1;1;1})
and returns {4; 3; 3; 2}.
Step 7 - Subtract with 1
The MMULT function calculates all overlapping ranges even the range itself. We need to subtract with 1 to get the correct result.
MMULT((C3:C6<=TRANSPOSE(D3:D6))*(D3:D6>=TRANSPOSE(C3:C6)),C3:C6^0)-1
becomes
{4; 3; 3; 2}-1
and returns {3; 2; 2; 1}.
2.1 Use a drop-down list to select a date range to see overlapping date ranges
In the animated picture above 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:
3. 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:
Want to know more about the excel functions used in the formulas above?
SUMPRODUCT, MMULT, INDEX, MATCH
4. Least overlapped date range
Array formula in cell B14:
5. Most overlapped date
Array formula in cell C18:
6. Least overlapped date
Array formula in cell C21:
Interested in learning more about excel, join my Advanced excel course.
Overlapping category
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Excel categories
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.
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)?