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.

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).

Get the Excel file

Filter-max-min-overlapping-date-from-overlapping-date-ranges-1.xlsx

Overlapping category

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
This article demonstrates formulas that show if a date range is overlapping another date range.  The second section shows how […]

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 […]

Excel categories

Comments (1)