### Introduction

This blog article describes how to extract coinciding date ranges.

Example,

### Array formula

Cell range B3:C25 contains example date ranges.

Overlapping date ranges

Array formula in cell E4:

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1, \$B\$3:\$B\$25, ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell E4 and paste down as far as needed.

Array formula in cell F4:

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1, \$C\$3:\$C\$25, ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell F4 and paste down as far as needed.

Date ranges without overlapping

Array formula in cell E17:

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)=1, \$B\$3:\$B\$25, ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell E17 and paste down as far as needed.

Array formula in cell F17:

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)=1, \$B\$3:\$B\$25, ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell F17 and paste down as far as needed.

### Explaining array formula in cell E4

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1, \$B\$3:\$B\$25, ""), ROW(A1))

Step 1 - Find overlapping date ranges

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1, \$B\$3:\$B\$25, ""), ROW(A1))

COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)

becomes

COUNTIFS({40109;40171;40210; 40264;40397;40405;40417;40457; 40500;40545;40611;40617;40726;40777;40791;40802;40831;40882;40994; 41014;41040;41127;41182}, "<="&{40137;40188;40268;40264;40401; 40413;40447;40517;40503; 40608;40613;40710;40773;40791;40817;40812;40875; 40927;41010;41085;41086;41168;41262}, \${40137;40188;40268;40264;40401; 40413;40447;40517;40503; 40608;40613;40710;40773;40791;40817;40812;40875;40927; 41010;41085;41086;41168;41262}, ">="&\${40109;40171;40210;40264;40397;40405; 40417;40457; 40500;40545;40611;40617;40726;40777;40791;40802;40831;40882;40994; 41014;41040;41127;41182}) and returns {1;1;2;2;1;1;1;2;2;1;1;1;1;2;3;2;1;1;1;2;2;1;1}

COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1

returns

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

Step 2 - Convert boolean array to start dates

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1, \$B\$3:\$B\$25, ""), ROW(A1))

IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1, \$B\$3:\$B\$25, "")

becomes

IF({FALSE;FALSE;TRUE;TRUE; FALSE;FALSE;FALSE;TRUE;TRUE;FALSE; FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE; FALSE;TRUE;TRUE;FALSE;FALSE}, {40109;40171;40210; 40264;40397;40405;40417;40457;40500; 40545;40611;40617;40726;40777;40791;40802;40831;40882; 40994;41014;41040;41127;41182}, "")

and returns

{"";"";40210;40264; "";"";"";40457;40500;"";""; "";"";40777;40791;40802;"";"";""; 41014;41040;"";""}

Step 3 - Return the k-th smallest number in array

SMALL(array,k) returns the k-th smallest number in this data set.

=SMALL(IF(COUNTIFS(\$B\$3:\$B\$25, "<="&\$C\$3:\$C\$25, \$C\$3:\$C\$25, ">="&\$B\$3:\$B\$25)>1, \$B\$3:\$B\$25, ""), ROW(A1))

becomes

=SMALL({"";"";40210;40264;"";""; "";40457;40500;"";"";"";"";40777;40791;40802;"";"";"";41014; 41040;"";""}, ROW(A1))

becomes

=SMALL({"";"";40210;40264;"";""; "";40457;40500;"";"";"";"";40777;40791;40802;"";"";""; 41014;41040;"";""}, 1) and returns 40210 (1-Feb-2010)

Filter overlapping date ranges.xlsx
(Excel 97-2003 Workbook *.xls)