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

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

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

Identify rows of overlapping records

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

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

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

Find empty dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Highlight date ranges overlapping selected record [VBA]

This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]

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

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

**Contact Oscar**

You can contact me through this contact form

This is invaluable information! Thanks!