Partial column formating

that is because rows 39-105 are not real XL Dates....

Starting in row 39 your dates are actually stored as text. Here's one way to convert them:

1. Select column H
2. Copy
3. Select I1
4. Paste, Paste Special, click under Operation, the Add button
5. Click OK
6. Cut column I and paste back to column H.

Now apply your date formatting.

you can also use the DATEVALUE formula to convert to real XL date and then apply your date formatting....just another way skin the kitty cat (not trying to offend any cat lovers)....

Originally Posted by Porsche

Thanks worked perfectly I tried this but did not use the ADD button or cut and past I used copy and paste.

Where did you get the data from in the first place? I suspect that's the source of the problem. I'd also hazard a guess that perhaps the data has been used with a different regional setting at some stage and a csv file created from it. It's probably not a coincidence that the last 'good' date on row 38 is equally valid whatever the regional setting, i.e. either 1st November 2014 (a normal uk setting) or 11th January as a US setting. Not the case with row 39 where the 14 can be a day but not a month number.

i wouldnt trust any of those dates! as Richard said some have resolved to dates and others not you need to see the original data to find out what format they were in seeing as row 39 onwards hasnt resolved it tends to suggest they were input in us format but to putt a spanner in the works row33 has resolved 13/02/2014
now if that had been input in us format it would say 02/13/2014 and be further down the list

Originally Posted by Porsche

I built the spread sheet for someone else and it's data was all entered by one user. It may have been manipulated by someone filling in for him. I do not know who imported the text dates.

...Mmmm

I suggest a classic case of GIGO

You'll need to go back and bring the data back to a consistent standard.