## List dates outside specified date ranges in excel

*Article 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

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 […]### 4 Responses to “List dates outside specified date ranges in excel”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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 ?