sort by date problem

[scudooder]

Hello

I am having this problem where I converted a flat text file into a
excel worksheet. There is a column for date and the format of the dat
is:


Nov 01 2000



Now, I want to sort all the columns by ascending date, but it wont wor
because I guess Excel sees the date column as text even after going t
'format cells' -> date. Is there a vba script that I can use tha
converts the date column into a data type that I can use to sort al
columns with?


thanks in advanc

 

[Ron Rosenfeld]

Hello

I am having this problem where I converted a flat text file into an
excel worksheet. There is a column for date and the format of the date
is:


Nov 01 2000



Now, I want to sort all the columns by ascending date, but it wont work
because I guess Excel sees the date column as text even after going to
'format cells' -> date. Is there a vba script that I can use that
converts the date column into a data type that I can use to sort all
columns with?


thanks in advance

Data/Text to Columns/Delimited (and leave everything blank)

Date MDY


--ron

 

[Peo Sjoblom]

If your dates always use one space as delimiter, 2 digits for the day and 4
for the year you can use a help column and this

=--(LEFT(TRIM(A1),3)&" "&MID(TRIM(A1),5,2)&", "&RIGHT(TRIM(A1),4))

then format as dates, copy and paste special as values and delete the other
column and sort on the new one

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[scudooder]

thanks guys, works good