sort by date problem

S

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
 
R

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
Click to expand...


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

Date MDY


--ron
 
P

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)
 
Top