In this post we are going create an array formula to calculate dates outside specified date ranges in cell range E17:E21 and I17:I21.

In a previous post we created a simple calendar and used conditional formatting to highlight dates within specified date ranges.

These dates are highlighted green as shown in picture below.

Dates outside specified date ranges in January 2010

Array formula in A24:

=SMALL(IF(FREQUENCY(IF((DATE(2010, 1, ROW($1:$31))>TRANSPOSE($I$17:$I$21))+(DATE(2010, 1, ROW($1:$31))<TRANSPOSE($E$17:$E$21)), DATE(2010, 1, ROW($1:$31)), ""), DATE(2010, 1, ROW($1:$31)))=ROWS($E$17:$E$21), DATE(2010, 1, ROW($1:$31)), ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell A24 and paste it down as far as needed. I guess this formula can be a lot smaller.

Download excel sample file for this tutorial.

Filter dates outside date-ranges.xls
(Excel 97-2003 Workbook *.xls)

Related articles:

Visualize date ranges in a calendar in excel

Find overlapping date ranges in excel

Highlight overlapping date ranges using conditional formatting in excel

Calendar with scheduling in excel 2007 (vba)

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

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

ROWS(array) returns the number of rows in a reference or an array

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code