text to date

[Donald]

I have a query extracting data from an AS400 library file, one of the
columns returns the date as "text" in the following manner mm/dd/yy

When I sort the query data by this column, the data is not sorted in the
proper date order as the data is sorted as "text". 01/01/05 is arranged
before 10/1/04 because 01 would normally precede 10.

Maybe someone can help me insert a column (H) to convert the text (column I)
to a real date that will be sorted as "dates" by normal calendar sequence.

Here's the code I'm presently working with in column H. (query data is in
column I)
=LEFT(I3,2)&"/"&MID(I3,4,2)&"/"&MID(I3,7,2)

 

[Donald]

Sorry, this was not a code question, rather a worksheet formula question.
Maybe there is a more appropriate forum?

 

[mangesh_yadav]

use
=DATE(2000+MID(I3,7,2),LEFT(I3,2),MID(I3,4,2))

- Manges

 

[Max]

Perhaps try this ..

Kill the formulas you have in col H with an "in-place":
Copy > paste special > values > OK

Select col H

Click Data > Text to Columns
Click Next > Next

In step3 of the wizard:
Under "Column data format":
Select "Date", and choose "MDY" under the droplist
Click Finish

The above will convert col H to "real" dates
which should now sort properly
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Sorry, this was not a code question, rather a worksheet formula question.
Maybe there is a more appropriate forum?

 

[Donald]

Thanks, looks like BOTH methods work.

--
Donald

Perhaps try this ..

Kill the formulas you have in col H with an "in-place":
Copy > paste special > values > OK

Select col H

Click Data > Text to Columns
Click Next > Next

In step3 of the wizard:
Under "Column data format":
Select "Date", and choose "MDY" under the droplist
Click Finish

The above will convert col H to "real" dates
which should now sort properly

 

[Max]

You're welcome !
Thanks for the feedback