Author: Oscar Cronquist Article last updated on July 27, 2017

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

Min and max overlapping date based on condition.xlsx

## Calculate the min and max date from overlapping dates 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