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.
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.
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.
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:
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
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
Returns the relative position of an item in an array that matches a specified value
Returns the reference specified by a text string
YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999