sorting on a date format - maybe?

D

dbh

I'm importing a text file with many columns of data that has been
generated by an external program. I have over 4000 rows, of which
several columns have a date format like this:

Fri Nov 5 09:41:36 2004

Somehow I need to be able to sort this column sanely, such that it can
be sorted
by the date.

I think that Excel needs to understand that a cell has a date (and
time?) in it, in order to convert it to a value that can be sorted.

I have looked at FAQs and tips, and I just can't seem to understand how
I might be able to do this.

I really need to be able to sort the whole file based on a sanely
ordered date column.

Any help would be appreciated.

Thanks,

deb
 
D

Dave Peterson

Just by the date???

How about using a helper column and using a formula like:

=DATEVALUE(MID(TRIM(A1),5,SEARCH(" ",TRIM(A1),10)-5)&", "&RIGHT(TRIM(A1),4))
(Format as date: mm/dd/yyyy)

If you want date and time, how about:

=DATEVALUE(MID(TRIM(A1),5,SEARCH(" ",TRIM(A1),10)-5)&", "&RIGHT(TRIM(A1),4))
+TIMEVALUE(MID(TRIM(A1),SEARCH(" ",TRIM(A1),10)+1,8))

(format as date & time: mm/dd/yyyy hh:mm:ss)
 
Top