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.

1. Filter overlapping date ranges

Overlapping date ranges

Array formula in cell E4:

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:

Copy cell F4 and paste down as far as needed.

Date ranges without overlapping

Array formula in cell E17:

Copy cell E17 and paste down as far as needed.

Array formula in cell F17:

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

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

=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

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

=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

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.

The ROW function keeps track of the numbers based on a relative cell reference. It will change as the formula is copied to the cells below.

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)

Get the Excel file

Filter-overlapping-date-ranges.xlsx

Back to top

2. Filter overlapping date ranges - Excel 365

This section demonstrates an Excel 365 formula that automatically spills values below and to the right as far as needed.

Excel 365 formula in cell E4:

Excel 365 formula in cell E16:

Explaining formula in cell E4

Step 1 - Concatenate logical characters with date values

The ampersand character lets you join characters in an Excel formula. This technique works also with arrays.

"<="&$C$3:$C$25

becomes

"<="&{44637; 44688; 44768; 44764; 44901; 44913; 44947; 45017; 45003; 45108; 45113; 45210; 45273; 45291; 45317; 45312; 45375; 45427; 45510; 45585; 45586; 45668; 45762},{44637; 44688; 44768; 44764; 44901; 44913; 44947; 45017; 45003; 45108; 45113; 45210; 45273; 45291; 45317; 45312; 45375; 45427; 45510; 45585; 45586; 45668; 45762}

and returns

{"<=44637"; "<=44688"; "<=44768"; "<=44764"; "<=44901"; "<=44913"; "<=44947"; "<=45017"; "<=45003"; "<=45108"; "<=45113"; "<=45210"; "<=45273"; "<=45291"; "<=45317"; "<=45312"; "<=45375"; "<=45427"; "<=45510"; "<=45585"; "<=45586"; "<=45668"; "<=45762"}.

Step 2 - Count overlapping date ranges

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

Function syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

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

becomes

COUNTIFS({44609; 44671; 44710; 44764; 44897; 44905; 44917; 44957; 45000; 45045; 45111; 45117; 45226; 45277; 45291; 45302; 45331; 45382; 45494; 45514; 45540; 45627; 45682},"<="&{44637; 44688; 44768; 44764; 44901; 44913; 44947; 45017; 45003; 45108; 45113; 45210; 45273; 45291; 45317; 45312; 45375; 45427; 45510; 45585; 45586; 45668; 45762},{44637; 44688; 44768; 44764; 44901; 44913; 44947; 45017; 45003; 45108; 45113; 45210; 45273; 45291; 45317; 45312; 45375; 45427; 45510; 45585; 45586; 45668; 45762},">="&{44609; 44671; 44710; 44764; 44897; 44905; 44917; 44957; 45000; 45045; 45111; 45117; 45226; 45277; 45291; 45302; 45331; 45382; 45494; 45514; 45540; 45627; 45682})

becomes

COUNTIFS({44609; 44671; 44710; 44764; 44897; 44905; 44917; 44957; 45000; 45045; 45111; 45117; 45226; 45277; 45291; 45302; 45331; 45382; 45494; 45514; 45540; 45627; 45682},{"<=44637"; "<=44688"; "<=44768"; "<=44764"; "<=44901"; "<=44913"; "<=44947"; "<=45017"; "<=45003"; "<=45108"; "<=45113"; "<=45210"; "<=45273"; "<=45291"; "<=45317"; "<=45312"; "<=45375"; "<=45427"; "<=45510"; "<=45585"; "<=45586"; "<=45668"; "<=45762"},{">=44609"; ">=44671"; ">=44710"; ">=44764"; ">=44897"; ">=44905"; ">=44917"; ">=44957"; ">=45000"; ">=45045"; ">=45111"; ">=45117"; ">=45226"; ">=45277"; ">=45291"; ">=45302"; ">=45331"; ">=45382"; ">=45494"; ">=45514"; ">=45540"; ">=45627"; ">=45682"})

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}

Step 3 - Return true if more than one overlapping date range

The larger than character is a logical character that returns true if the condition is met and false if not.

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

becomes

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

and 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 4 - Extract rows based on boolean values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

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

becomes

FILTER(B3:C25, {FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE})

and returns

{44710,44768; 44764,44764; 44957,45017; 45000,45003; 45277,45291; 45291,45317; 45302,45312; 45514,45585; 45540,45586}

Back to top

Overlapping category

Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Identify overlapping date ranges
This article demonstrates formulas that show if a date range is overlapping another date range.  The second section shows how […]

How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Excel categories

Comments (1)