## Find missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that are between date ranges.

I have also built a small calendar using conditional formatting to show exactly where the missing dates are. The formula below works fine with overlapping date ranges.

I will explain the formula in this article and there will also be a file for you to download.

Array formula in cell B8:

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.

Now copy cell B8 and paste as far as needed to cells below.

### How to adjust cell references in the array formula to your worksheet

Cell range $B$3:$B$5 is the start dates of the date ranges and $C$3:$C$5 contains the end dates.

$B$3:$C$5 contains both start and end dates of your date ranges. Adjust these accordingly to your worksheet and don't forget to enter the formula as an array formula.

$A$1:A1 is only an expanding cell reference that lets the SMALL function extract the correct date value, you don't need to change it.

### Explaining formula in cell B8

#### Step 1 - Create an array of Excel dates

The following formula returns an array of Excel dates needed to extract the missing dates.

ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5)))

becomes

ROW(INDIRECT(43102&":"&43114))

becomes

ROW(INDIRECT(43102:&43114))

and returns {43102; 43103; 43104; 43105; 43106; 43107; 43108; 43109; 43110; 43111; 43112; 43113; 43114}.

#### Step 2 - COUNTIFS function returns an array that indicates which dates are outside the date ranges

The COUNTIFS function returns an array that we can use to extract dates not in date ranges. This particular COUNTIFS function has 4 arguments, however, you can use up to 255 arguments.

COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))

becomes

COUNTIFS($B$3:$B$5,{"<=43102"; "<=43103"; "<=43104"; "<=43105"; "<=43106"; "<=43107"; "<=43108"; "<=43109"; "<=43110"; "<=43111"; "<=43112"; "<=43113"; "<=43114"},$C$3:$C$5,{">=43102"; ">=43103"; ">=43104"; ">=43105"; ">=43106"; ">=43107"; ">=43108"; ">=43109"; ">=43110"; ">=43111"; ">=43112"; ">=43113"; ">=43114"})

and returns {1; 1; 1; 0; 0; 1; 1; 0; 0; 0; 1; 1; 1}.

#### Step 3 - Compare each value in array with 0 (zero)

Value 0 (zero) shows us that the corresponding date is not in the date range so I am now going to compare each value in the array to 0 (zero).

COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))=0

becomes

{1; 1; 1; 0; 0; 1; 1; 0; 0; 0; 1; 1; 1}=0

and returns {FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE}.

#### Step 4 - IF function returns an array of correct dates

The IF function uses the logical values to filter the dates we are looking for.

IF(COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))=0, ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE}, {43102; 43103; 43104; 43105; 43106; 43107; 43108; 43109; 43110; 43111; 43112; 43113; 43114})

and returns {FALSE; FALSE; FALSE; 43105; 43106; FALSE; FALSE; 43109; 43110; 43111; FALSE; FALSE; FALSE}

#### Step 5 - Extract the k-th smallest number (date)

The SMALL function returns dates based on their sizes, the second argument uses an expanding cell reference so that the small function extracts the smallest value in cell B8 and the second smallest in cell B9 and so on.

SMALL(IF(COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))=0, ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5)))), ROWS($A$1:A1))

becomes

SMALL({FALSE; FALSE; FALSE; 43105; 43106; FALSE; FALSE; 43109; 43110; 43111; FALSE; FALSE; FALSE}, ROWS($A$1:A1))

becomes

SMALL({FALSE; FALSE; FALSE; 43105; 43106; FALSE; FALSE; 43109; 43110; 43111; FALSE; FALSE; FALSE}, 1)

and returns 43105 in cell B8.

Excel formats the number as a date and shows 1/5/2018, see picture below.

### Download excel *.xlsx file

Question: I am trying to create an excel spreadsheet that has a date range.Example: Cell A1 1/4/2009-1/10/2009 Cell B1 1/11/2009-1/17/2009 […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Identify missing numbers in a column

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Identify missing numbers in two columns based on a numerical range

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

Insert rows for missing values

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

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 […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

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, […]

### 3 Responses to “Find missing dates in a set of date ranges”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Excellent. Thanks.

I just had to say that the 'Find the missing dates' formula is beautifully elegant and can be adapted to any series in sequential order (numbers, text etc).

Thank you very much for this gem.

Hello

what do i do if i want to compare the dates i've found as a result of your excellent formula to another table of date range and filter them.

For example i have found 1/7/2014

2/7/2014

3/7/2014

31/12/2015

13/6/2015

19/9/2015

and i need to find out which dates are between start: end:

1/6/2013 12/5/2015

17/6/2015 31/12/2016

Thank you !