Author: Oscar Cronquist Article last updated on January 11, 2019

This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges in cell range B3:C25.

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

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

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

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

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