## Find missing dates in a set of date ranges

*Article updated on September 20, 2017*

John S asks:

example:

2/3/2005 - 2/5/2005

2/7/2005 - 2/9/2005

_____________

Missing dates:

2/4/2005, 2/6/2005,2/8/2005

**Array formula in cell A6:**

Functions in this array formula: SMALL, IF, ROW, INDIRECT and COUNTIF

### Find dates between date ranges

**Array formula in cell A6:**

Functions in this array formula: SMALL, IF, ROW, INDIRECT and COUNTIFS

Interested in learning more about excel, join my Advanced excel course.

### 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 […]Count records between two dates with multiple parameters in excel

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]Identify missing three character alpha code numbers in excel

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]### 3 Responses to “Find missing dates in a set of date ranges”

### Leave a Reply

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

<code>your formula</code>

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

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 !