Liz asks:

I want to identify the overlap based on a criteria but now I want to know what is that min date and the max date. Any tricks up your sleeve?

Identify min and max date among overlapping date ranges and based on a condition

The following formulas calculates the min and max date from overlapping date ranges:

Array formula in cell F2:

=MIN(IF(($A$2:$A$9=F1)*(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1, $B$2:$C$9, ""))

Array formula in cell F3:

=MAX(IF(($A$2:$A$9=F1)*(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1, $B$2:$C$9, ""))

Explaining array formula in cell F2

Step 1 - Find rows with overlapping dates based on condition

(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1

becomes

{2;1;2;2;2;2;1;2}>1

and returns

{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}

Step 2 - Check if cell range $A$2:$A$9 is equal to cell value in cell F1

$A$2:$A$9=F1

becomes

{"A";"A";"A";"B";"C";"B";"C";"C"}="A"

and returns

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

Step 3 - Filter dates

IF(($A$2:$A$9=F1)*(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1, $B$2:$C$9, "")

becomes

IF({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}, $B$2:$C$9, "")

and returns

{41275,41279;"","";41278,41280;"","";"","";"","";"","";"",""}

Step 4 - Minimum date

MIN(IF(($A$2:$A$9=F1)*(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1, $B$2:$C$9, ""))

becomes

MIN({41275,41279;"","";41278,41280;"","";"","";"","";"","";"",""})

and returns 41275 (2013-01-01) in cell F2.

Download *.xlsx file

Min and max overlapping date based on condition.xlsx

Calculate the min and max date from overlapping dates

Identify min and max date among overlapping dates and based on a condition

The following formulas calculates the min and max date from overlapping dates:

Array formula in cell A2:

=MAX(IF(FREQUENCY(IF((MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1<=IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))+(MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1>=IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))>1, MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1, ""), MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1)>1, MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+ROW(OFFSET($A$1, , 0, MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))))-1, ""))

Array formula in cell A4:

=MAX(IF(FREQUENCY(IF((MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1<=IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))+(MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1>=IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))>1, MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1, ""), MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+COLUMN(OFFSET($A$1, , 0, , MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+1))-1)>1, MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))+ROW(OFFSET($A$1, , 0, MAX(IF($D$2:$D$6=$B$2, $F$2:$F$6, ""))-MIN(IF($D$2:$D$6=$B$2, $E$2:$E$6, ""))))-1, ""))

Explaining array formula in cell A2

Read this post: Filter overlapping dates from date ranges in excel

Download excel *.xlsx file

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

Functions in this post

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE