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

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

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

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

### 2 Responses to “How to calculate missing months in a given date range”

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

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/