Converting Text to Date & Sorting on Date

Hi everyone.
I am trying to convert a long list of dates from text to date (see A) so that I can sort them from newest to oldest. I've tried converting them using differnt formulas (see B:C) but these don't work.
Any suggestions?

try this in D2, then copy and paste down

Please Login or Register  to view this content.

You can miss out the TEXT(value,"mm/dd/yyyy") if you just want to format the column to your required date formate

The other part of the formula does..........

When the string length is 20, this means the day value is only 1 number, like 3rd, 4th, 5th
When the string length is 21, this means the day value has 2 numbers, like 23rd, 24th, 25th

Once it's established this, it takes each element of the string value using MID to put the right values into the DATEVALUE formula

oh, btw, this presumes all new date values you'll be working with will always presented in exactly the same format, if you are aware there might be variations let us know and we'll put additional tests into the formula

Thanks easyexcelanswers but I still can't sort from oldest to newest. It will only allow sorting from A-Z which prevents me from using the data.

Originally Posted by andresndor

Thanks easyexcelanswers but I still can't sort from oldest to newest. It will only allow sorting from A-Z which prevents me from using the data.

Sorry, I made a mistake by using the TEXT in the formula, you need to take that out of the formula

Please Login or Register  to view this content.

Then format the column to the correct date format you want

It will then allow sorting as expected

Thanks for the help everyone.

I used XOR LX's formula because I needed to make some adjustments and that made the most sense to me.

But I really appreciate all the other responses.