Find missing dates

[mrcuser]

Hi I have a spreadsheet that contains stock level checks done by an employee but I need to find and list any dates that are missing.
The column is F1 and the date starts at 12/05/2008 all the way through to 26/08/2008.

Any simple way of doing this?

 

[GlennUK]

What dates are supposed to be in the list ( every day, or exclude Sat and Sun, and what about Bank Holidays )? Is there one row per date?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[mrcuser]

Hi Glenn, thanks for replying.
The dates should read everyday.
The file has dates for each day (sometimes several times) There is one row per date (but some could be duplicated. Column looks like this:

18/01/2008
18/01/2008
18/01/2008
18/01/2008
19/01/2008
19/01/2008
21/01/2008

So i'd be after finding the date 20/01/2008 in this example.

 

[theo1881]

not a perfect solution i know but you could subtotal the date column and then collapse that grouping and you would only have one of each date and the missing dates will be easier to spot (providing your not searching years of data)

Laters, phat, headshape

 

[GlennUK]

If the dates are in order ( like your example ), then a helper column of =F2-F1 in e.g. G2, copied down, will yield a list of 0's, and 1's for data that is OK, and numbers that are greater than 1 when a date is missing. You can then use conditional formatting to highlight those cells or even filter on the results.

Cheers, Glenn.

Beauty is in the eye of the beerholder.