## Filter overlapping date ranges in excel 2007

### 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:

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*

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

### Download excel sample file

Filter overlapping date ranges.xlsx

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test,[value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

**SMALL(**array,k)

Returns the k-th smallest number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

### Category: Overlapping

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Comments(31) Filed in category: Excel, Overlapping

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]Comments(24) Filed in category: Excel, Overlapping

Highlight duplicate values and overlapping dates in excel

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]Comments(23) Filed in category: Excel, Overlapping

Visualize date ranges in a calendar

Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible […]Comments(10) Filed in category: Calendar, Excel, Overlapping

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]Comments(8) Filed in category: Dates, Excel, Overlapping

Comments(7) Filed in category: Excel, Overlapping

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]Comments(7) Filed in category: Excel, Overlapping

Highlight events overlapping federal holidays

Bryan asks: i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart) i have […]Comments(6) Filed in category: Excel, Overlapping

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In […]Comments(4) Filed in category: Overlapping

Count overlapping dates in excel

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]Comments(4) Filed in category: Excel, Overlapping