## List dates outside specified date ranges in excel

*Article last updated on October 22, 2012*

I don´t think I have explained how to filter dates outside date ranges.

*Example,*

Table 1 contains start and end dates for each date range.

Cell B13 is the start date and B14 is the end date. The array formula below filters all dates between the start and end date and outside the specified date ranges in table1.

**Array formula in cell B16:**

**How to create an array formula**

- Copy above array formula
- Click in formula bar
- Paste array formula (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Select cell B16
- Copy cell (Ctrl + c)
- Select cell range B17:B25
- Paste (Ctrl + v)

Here is a picture where I have plotted date ranges. Red "x" are dates outside date ranges.

### Download excel *.xlsx file

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

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]

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

Lookup two index columns in excel

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

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

### 4 Responses to “List dates outside specified date ranges in excel”

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

typo??

INDEX($A:$A, $B$14-$B$13) --> INDEX($A:$A, $B$14-$B$13+1)

aMareis,

I don´t think so. Can you explain why?

If B9=2012-01-30 and B10=2012-01-30 then ????

Your result omit 2012-01-31.

i don't know but it does not work for me for 180 lines of range dates can you please help me ?