Author: Oscar Cronquist Article last updated on December 28, 2018

The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E.

Array formula in B3:

=SMALL(IF(FREQUENCY(IF((MIN($D$3:$D$7)+COLUMN(OFFSET($B$2, , 0, , MAX($E$3:$E$7)-MIN($D$3:$D$7)+1))-1<=$E$3:$E$7)+(MIN($D$3:$D$7)+COLUMN(OFFSET($B$2, , 0, , MAX($E$3:$E$7)-MIN($D$3:$D$7)+1))-1>=$D$3:$D$7)>1, MIN($D$3:$D$7)+COLUMN(OFFSET($B$2, , 0, , MAX($E$3:$E$7)-MIN($D$3:$D$7)+1))-1, ""), MIN($D$3:$D$7)+COLUMN(OFFSET($B$2, , 0, , MAX($E$3:$E$7)-MIN($D$3:$D$7)+1))-1)>1, MIN($D$3:$D$7)+ROW(OFFSET($B$2, , 0, MAX($E$3:$E$7)-MIN($D$3:$D$7)))-1, ""), ROW(A1))

New smaller formula:

=SMALL(IF(COUNTIFS($D$3:$D$7, "<="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, $E$3:$E$7, ">="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1)>1, MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, ""), ROWS($B$1:B1))

You can find an explanation for the new formula in this article: Convert date ranges into dates The only difference is that this formula extracts dates that exist in more than one date range.

How does this (old) formula work?

Step 1 - Create dates and check if they are smaller or equal to dates in cell range $D$2:$D$6

(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End

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}<=End

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}<={40181;40187;40189;40207;40210}

and returns

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

Step 2 - Create dates and check if they are larger or equal to dates in cell range $C$2:$C$6

(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=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}<=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}<={40179;40180;40189;40191;40209}

and returns

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

Step 3 - Add arrays and check if they are larger than 1

(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1

becomes

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

and returns

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

Step 4 - Filter overlapping dates

IF((MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1, "")

becomes

IF({TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}, {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}, "")

and returns

{40179, 40180, 40181, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", 40180, 40181, 40182, 40183, 40184, 40185, 40186, 40187, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", 40189, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "", 40191, 40192, 40193, 40194, 40195, 40196, 40197, 40198, 40199, 40200, 40201, 40202, 40203, 40204, 40205, 40206, 40207, "", "", "";"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 40209, 40210}

Step 5 - Calculate how often values occur

FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1, ""), MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1)

becomes

FREQUENCY({40179, 40180, 40181, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", 40180, 40181, 40182, 40183, 40184, 40185, 40186, 40187, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", 40189, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "", 40191, 40192, 40193, 40194, 40195, 40196, 40197, 40198, 40199, 40200, 40201, 40202, 40203, 40204, 40205, 40206, 40207, "", "", "";"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 40209, 40210}, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1)

becomes

FREQUENCY({40179, 40180, 40181, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", 40180, 40181, 40182, 40183, 40184, 40185, 40186, 40187, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";40179, 40180, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "", 40191, 40192, 40193, 40194, 40195, 40196, 40197, 40198, 40199, 40200, 40201, 40202, 40203, 40204, 40205, 40206, 40207, "", "", "";"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 40204, 40205, 40206, 40207, 40208, 40209, 40210}, {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})

and returns

{1; 2; 2; 1; 1; 1; 1; 1; 1; 0; 1; 0; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 0; 1; 1; 0}

Step 6 - Check if vaues are larger than 1

IF(FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1, ""), MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1)>1, MIN(Start)+ROW(OFFSET($A$1, , 0, MAX(End)-MIN(Start)))-1, "")

becomes

IF({1; 2; 2; 1; 1; 1; 1; 1; 1; 0; 1; 0; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 0; 1; 1; 0}>1, MIN(Start)+ROW(OFFSET($A$1, , 0, MAX(End)-MIN(Start)))-1, "")

becomes

IF({1; 2; 2; 1; 1; 1; 1; 1; 1; 0; 1; 0; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 0; 1; 1; 0}>1, {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}, "")

and returns

{"";40180;40181;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

Step 7 - Return the k-th smallest value

SMALL(IF(FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1, ""), MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1)>1, MIN(Start)+ROW(OFFSET($A$1, , 0, MAX(End)-MIN(Start)))-1, ""), ROW(A1))

becomes

SMALL({"";40180;40181;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}, ROW(A1))

and returns 40180 (2-Jan-2010) in cell A2.

Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!