Find earliest and latest overlapping dates in a set of date ranges based on a condition
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping date ranges based on a condition.
The image above shows date ranges in cell range D3:E7, the condition is in cell H2. The condition is matched to cell range C3:C7.
Row 9 shows dates from 1/1/2010 to 1/27/2010. Row 10 shows date range #1 across dates. Row 11 shows date range #2. Date range #3 is not highlighted, it does not match the condition specified in cell H2. The formula is ignoring this date range.
Row 13 shows date range #4 and date range #5 is not overlapping any date range at all.
The calendar makes it easy to spot the earliest and latest overlapping date, this will make it easy to verify the outcome of the formula.
I want to identify the overlap based on criteria but now I want to know what is that min date and the max date. Any tricks up your sleeve?
The following formulas calculates the min and max date from overlapping date ranges:
Array formula in cell H3:
Array formula in cell H4:
I recommend Excel Tables, they allow you to add and delete date ranges without the need to adjust cell references in the above formulas. References to Excel Tables are called "structured references" and they look differently than regular cell references.
Here is an structured reference example: Table1[Category] It is a reference to data in column Category in Excel Table named Table1. It begins with the Excel Table name and then a beginning bracket, the column header name and an ending bracket.
Explaining array formula in cell H3
Step 1 - Create an array from 0 (zero) to n
This step creates numbers from 0 (zero) to n that we will use, in the next step, to create all dates between the earliest date and the latest date based on the date ranges we are working with.
The MAX function calculates the largest (latest) date from dates in column End: in Excel Table named Table1
MAX(Table1[End:])
becomes
MAX({40181; 40187; 40180; 40207; 40238})
and returns 40238.
The MIN function returns the smallest number from column Start: in Excel Table Table1.
MIN(Table1[Start:])
becomes
MIN({40179;40180;40179;40186;40235})
and returns 40179.
The INDEX function returns a cell reference based on the number of days there are between the earliest date and the latest date.
INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1)
becomes
INDEX($1:$1, 40238-40179+1)
becomes
INDEX($1:$1, 59+1)
becomes
INDEX($1:$1, 60)
returns cell reference BH1.
The colon character allows you to concatenate two cell references.
$A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))
becomes
$A$1:BH1
The column function calculates the column numbers from each cell in cell range $A$1:BH1.
COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))
becomes
COLUMN($A$1:BH1)
and returns
{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60}
Step 2 - Calculate dates
If we add array of numbers, calculated in the previous step, to a date we get an array of dates.
MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1
becomes
MIN(Table1[Start:])+{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60}-1
becomes
MIN(Table1[Start:])+{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59}
and returns
{40179, 40180, 40181, 40182, 40183, 40184, 40185, 40186, 40187, 40188, 40189, 40190, 40191, 40192, 40193, 40194, 40195, 40196, 40197, 40198, 40199, 40200, 40201, 40202, 40203, 40204, 40205, 40206, 40207, 40208, 40209, 40210, 40211, 40212, 40213, 40214, 40215, 40216, 40217, 40218, 40219, 40220, 40221, 40222, 40223, 40224, 40225, 40226, 40227, 40228, 40229, 40230, 40231, 40232, 40233, 40234, 40235, 40236, 40237, 40238}
We will be using this array of dates to check which dates overlap each other.
Step 3 - Compare date array to start dates
MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:]
becomes
{40179, 40180, 40181, 40182, 40183, 40184, 40185, 40186, 40187, 40188, 40189, 40190, 40191, 40192, 40193, 40194, 40195, 40196, 40197, 40198, 40199, 40200, 40201, 40202, 40203, 40204, 40205, 40206, 40207, 40208, 40209, 40210, 40211, 40212, 40213, 40214, 40215, 40216, 40217, 40218, 40219, 40220, 40221, 40222, 40223, 40224, 40225, 40226, 40227, 40228, 40229, 40230, 40231, 40232, 40233, 40234, 40235, 40236, 40237, 40238}>=Table1[Start:]
becomes
{40179, 40180, 40181, 40182, 40183, 40184, 40185, 40186, 40187, 40188, 40189, 40190, 40191, 40192, 40193, 40194, 40195, 40196, 40197, 40198, 40199, 40200, 40201, 40202, 40203, 40204, 40205, 40206, 40207, 40208, 40209, 40210, 40211, 40212, 40213, 40214, 40215, 40216, 40217, 40218, 40219, 40220, 40221, 40222, 40223, 40224, 40225, 40226, 40227, 40228, 40229, 40230, 40231, 40232, 40233, 40234, 40235, 40236, 40237, 40238}>={40179; 40180; 40179; 40186; 40235}
and returns {TRUE, TRUE, TRUE, ... , TRUE}
This array is shortened for obvious reasons.
Step 5 - Compare date array to end dates
(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:])
returns {TRUE, TRUE, TRUE, ... ,TRUE}.
Step 6 - Multiply arrays
We want both conditions to be met, in order to do that we must multiply the arrays (AND logic)
(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:])*((MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:])
becomes
{TRUE, TRUE, TRUE, ... , TRUE} * {TRUE, TRUE, TRUE, ... , TRUE}
and returns {1, 1, 1, ... , 1}.
The equivalent value of boolean value TRUE is 1 and FALSE is 0 (zero). Excel converts the boolean values when you perform arithmetic operations.
Step 7 - Calculate which rows meet the condition
The equal sign allows you to compare a value to a value or in this case an array of values to a value.
(Table1[Category]=$H$2)
becomes
{"A";"A";"B";"A";"C"}="A"
and returns {TRUE; TRUE; FALSE; TRUE; FALSE}.
Step 6 - Multiply arrays
(Table1[Category]=$H$2)*(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:])*((MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:])
becomes
{TRUE; TRUE; FALSE; TRUE; FALSE} * {1, 1, 1, ... , 1}
and returns {1, 1, 1, ... , 0}.
Keep in mind that a semicolon separates values row-wise and commas respectively column-wise.
Step 7 - Create an array of 1's.
The power of character ^ converts all dates in Table column Start: to 1.
Table1[Start:]^0
becomes
{1; 1; 1; 1; 1}
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa.
TRANSPOSE(Table1[Start:]^0)
becomes
TRANSPOSE({1; 1; 1; 1; 1})
and returns {1, 1, 1, 1, 1}.
Step 8 - Add values vertically
MMULT(TRANSPOSE(Table1[Start:]^0), (Table1[Category]=$H$2)*(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:])*((MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:]))
becomes
MMULT({1, 1, 1, 1, 1}, (Table1[Category]=$H$2)*(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:])*((MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:]))
becomes
MMULT({1, 1, 1, 1, 1}, {1, 1, 1, ... , 1})
and returns {1, 2, 2, ... , 0}.
Step 9 - Check if value is above 1
A number above 1 indicates it is an overlapping date. The position of each number in the array corresponds to date in the date array. We can use this to extract the overlapping dates in the next step.
MMULT(TRANSPOSE(Table1[Start:]^0), (Table1[Category]=$H$2)*(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:])*((MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:]))>1
becomes
{1, 2, 2, ... , 0}>1
and returns {FALSE, TRUE, TRUE, ... , FALSE}
Step 10 - Convert boolean values to corresponding dates
The IF function determines if a date will be returned or nothing "" based on the logical expression.
IF(MMULT(TRANSPOSE(Table1[Start:]^0), (Table1[Category]=$H$2)*(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:])*((MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:]))>1, MIN(Start)+COLUMN($A$1:INDEX($1:$1, 0, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1, "")
becomes
IF({FALSE, TRUE, TRUE, ... , FALSE}, MIN(Start)+COLUMN($A$1:INDEX($1:$1, 0, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1, "")
becomes
IF({FALSE, TRUE, TRUE, ... , FALSE}, {40179, 40180, 40181, ... , 40238}, "")
and returns
{"", 40180, 40181, ... , "}.
Step 11 - Extract the latest date
The MAX function returns the largest date from the array ignoring blanks.
MAX(IF(MMULT(TRANSPOSE(Table1[Start:]^0), (Table1[Category]=$H$2)*(MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1>=Table1[Start:])*((MIN(Table1[Start:])+COLUMN($A$1:INDEX($1:$1, MAX(Table1[End:])-MIN(Table1[Start:])+1))-1)<=Table1[End:]))>1,MIN(Start)+COLUMN($A$1:INDEX($1:$1,0,MAX(Table1[End:])-MIN(Table1[Start:])+1))-1,""))
becomes
MAX({"", 40180, 40181, ... , "})
and returns 40187 (1/9/2010).
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 “Find earliest and latest overlapping dates in a set of date ranges based on a condition”
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.
This is invaluable information! Thanks!