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.

calculate missing months1

Answer:

calculate missing months2

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

Smallest and largest date value

Formula in D14:

=MAX(Date_col) + ENTER

Formula in D15:

=MIN(Date_col) + ENTER

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

Missing months

Array formula in B20:

=TEXT(DATE(YEAR($D$15), MONTH($D$15)+SMALL(IF(FREQUENCY((YEAR(Date_col)-YEAR($D$15))*12-MONTH($D$15)+MONTH(Date_col), ROW(INDIRECT("1:"&(YEAR($D$14)-YEAR($D$15))*12+MONTH($D$14)-MONTH($D$15)))-1)=0, ROW(INDIRECT("1:"&(YEAR($D$14)-YEAR($D$15))*12+MONTH($D$14)-MONTH($D$15)+1)), ""), ROW(1:1)), 1)-1, "mmm-yyyy") + CTRL + SHIFT + ENTER

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:

Related posts:

Excel: Calculate last date of a specific month

Create unique distinct year and months from a long date listing in excel

Count unique distinct months in excel

Highlight odd/even months using conditional formatting in excel 2007

Count matching cells in date range in excel