Excel formatting some dates, not others

I started out with an excel sheet that I coped a bunch of dates into one column. The problem was these dates copied in with not just the dates, but ahead of every date, the day of the week the date was, in abbreviated fashion.

For example, FRI OCT 21, 2011, is how one date would read. Every date was like this, except with THU or TUE or SAT ahead of the dates. So when I tried to sort the dates, they wouldn't sort by date, they would sort by day of the week. This was a problem for me.

To get around this problem, I thought I would use the replace function. I put in "FRI" and replaced it with nothing (basically deleting FRI), then SAT, then SUN, and I did this for every day of the week.

Before i knew it, not only were the days of the week gone, but excel had automatically formatted my dates, for some reason. Instead of OCT 21, 2011 excel changed it to 21-Oct-11.

Even though I dont understand the mechanism behind this change, it worked fine. I could now sort the dates. Except, at the very end, I noticed about 15 of the dates were unchanged, still in the "OCT 21, 2011" format. If the column had been entirely that way, or entirely the other way, either would have been fine, I believe. But because there are two different formats, now I still cant sort the dates in order.

How do I fix this? Why did some of them become unaltered? I even tried highlighting them, and manually going to format, dates, and selecting the 21-Oct-11 way, and it did nothing!

Here is a dummy sheet so you can see.

Definitely there is a better way. Try this =IF(T(A2)="",A2,DATEVALUE(A2))
drag the formula and then convert to values. Then give it date format of your choice. or copy and paste special date format from a-column. Now it will arrange properly.
The reason it is not arranging is because excel is understanding some as dates and some as simple text. The above formula first checks if its text , if true it leaves it as it is and if its not it converts to date value.

Originally Posted by martindwilson

=DATEVALUE(A70) does not work did you test it? the function requires text in the format 12/21/2000

I tested it again and its working. Meanwhile I thought of an easier way:

If objective is to arrange the dates then you can make an adjoining B column and name header as Value. In second cell use function =value(A2) and arrange this Value column and the original column would arrange automatically.