Find all missing dates between two dates in excel 2016

Solution 1:

Doing this with an array formula with Excel 2016 will be quite involved.

Why not use an easier approach that doesn't involve complicated formulas or VBA?

Create a list of dates from the start date to the end date. In the next column, use a Match formula to find the date in your data. You could use a formula that returns a TRUE/FALSE result, like

=isnumber(Match(A1,$Z:$Z,0))

If the result of the match is a number, i.e. the date is found, then the formula returns TRUE, otherwise FALSE.

Now you just need to filter the list of dates to show only the rows with "FALSE" in the formula column.

That's a lot faster than writing VBA or complex arrays.

Note: this will only work if your dates are dates only, not date/time values. If you have date/time values (which show decimals if formatted as General), then you need a helper column in your data source that extracts only the date. Then run the Match() on that date column.