## How to calculate missing months in a given date range

*Article updated on September 20, 2017*

**Question:** I have dates in a list. I would like to know how to identify missing months in this list and in a given date range? The given date range would, in this case, be the smallest date value and the largest date value in this list.

**Answer:**

To create a list of missing months we need to know the date range.

### Smallest and largest date value

Formula in D14:

Formula in D15:

To use any another date range, change date values in cell D14 and D15.

### Missing months

Array formula in B20:

Now this array formula uses INDIRECT function. It is an volatile function and should be avoided. I can´t figure out an array formula that doesn´t use INDIRECT function. It all boils down to the creation of an array that is equally large as the number of months in any given date range. If you know how to avoid INDIRECT in this formula, please comment.

**Named ranges**

Date_col (B3:B12)

What is named ranges?

### Download excel example file

calculate missing months in a given date range.xls

(Excel 97-2003 Workbook *.xls)

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

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

**ROW(**reference**)** returns the rownumber of a reference

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDIRECT(**ref_text;[a1]**)**

Returns the reference specified by a text string

**YEAR(**serial_number**)** returns the year of a date, an integer of the range 1900-9999

### Related articles:

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 […]### 2 Responses to “How to calculate missing months in a given date range”

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

I would like to find the dates MM/DD/YYYY missing in a set of date ranges. I haven't been able to find any luck on -line with this.

example:

2/3/2005 - 2/5/2005

2/7/2005 - 2/9/2005

_____________

Missing dates:

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

Please help.

John s,

read this:

https://www.get-digital-help.com/2015/05/13/find-missing-dates-in-a-set-of-date-ranges/