Weird compatability issue with dates from old version of Excel to new 2013 version

I hope I can explain this problem ok. I would be grateful if someone could point me in the right direction please ?

I get daily reports from a manufacturing system software in Excel format that includes a column of dates which are formatted as General cells. These are big reports that I use autofilter to sift through and review. However the dates are formatted in such a way that the autofilter doesn't group the dates and months together. Instead they appear in the autofilter drop-down in strict numerical order like this...

dates.jpg

I've tried to convert this spreadsheet to an .xlsw, I've also tried to format the column as a Date column to no avail.

The only thing that seems to work is to individually F2 each date cell, then press ENTER. This has the effect of making the date change in some way which the autofilter can make sense of. I did the F2 ENTER on a few cells which worked as follows :-

Dates2.jpg

Is there an easy way to fix all these dates ? Or is there a piece of VBA code that I can set to run from the top of the column and do the F2 ENTER routine on each cell until it reaches the last cell ?

If it helps to explain, here is a snapshot of the dates with some of them F2 ENTER'd

dates3.jpg

Hey Terry,

This looks like some of your dates are text while others are numbers. If you add zero to the text dates it will turn them into numbers and they will become dates (I hope).

To do that, there is this easy method. You put a zero in a blank cell somewhere on your sheet and copy it to the clipboard using Ctrl+C. Then select the whole column with your dates in them and then Right Click on the selected column. Then click on "Paste Special" from the right click dropdown and click on "Operation Add" radio button. Then OK. This will add zero to all the stuff in your date column and change them to numbers instead of text (I hope).

See http://www.contextures.com/xlDataEntry03.html for more info.

Let me know what happens.

Thank you Marvin that worked a treat in the end I had to add an extra step in there to format the column as 'date'...

With these reports I have a macro that I've recorded which finesses the formatting and puts in a pivottable for me. I'll record the above steps and add that in as well.