# Finding missing dates in column

I have a column of dates incrementing a day at a time. Would like to find if any date/s is/are missing from the list. How can I achieve it?

Preman

|

Assume your source dates are running in A1 down (dates must be real dates recognized by Excel)
Assume that the earliest date in col A will be the presumed startdate and the latest date, the presumed enddate
In B1:
=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))

In C1: =IF(ROWS(\$1:1)>COUNT(B:B),"",SMALL(B:B,ROWS(\$1:1)))
Copy B1:C1 down until blanks appear continuously in col B
Format col C as dates. Col C will return all the missing dates, neatly packed at the top

·

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

One method of noting non-sequential dates would be through the use of Conditional Formatting (CF) to highlight cell containing dates out of sequence.

Suppose your column of dates in in column A with the first date in A1 and the entire range of dates is A1:A100. Select A2:A100(not A1:A100), then use CF to create a new rule. Select Use a formula to determine which cells to format, then put this formula into Format values where this formula is true:.

=A2<>(A1+1)

... and click the Format... button to select cell highlighting, etc. Click OK on the way out to accept the new rule.

Any date that is not 1 day higher than the one above it should exhibit the highlighting you chose.

• "Have you tried turning it off and then on again?" - Roy from 'The IT Crowd' (Emmy award-winning British comedy)

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?