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?

Thanks for reading the post.

Preman

Answer
Answer

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 

14 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

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)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated March 30, 2021 Views 10,259 Applies to: