Search
Excel Formula to Find Dates Omitted from Range
If you have dates listed in a column in Excel, and want to find the dates which are omitted in that column between the first date and the last date, you can use two formulas to find the results.
Begin by sorting dates listed in column A in date order.
Then enter this formula in column B:
=IF(ROWS($1:1)>MAX(A:A)-MIN(A:A)+1,"",IF(ISNUMBER(MATCH(MIN(A:A)+ROWS($1:1)-1,A:A,0)),"",MIN(A:A)+ROWS($1:1)-1))
Using CTRL + D pull down this formula until it gives blank results after it generates the last date omitted from the range in column A. This formula will generate all of the missing dates, and leave a blank cell where a date is present in column A.
Then in column C, enter this formula:
=IF(ROWS($1:1)>COUNT(B:B),"",SMALL(B:B,ROWS($1:1)))
Pull this down to the end of the worksheet, and it will generate a list of just the omitted dates, without the blank cells listed in column B.
