text to date
 

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

text to date
 

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

--
Donald
"Donald" wrote in message
...
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

text to date
 

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

- Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=27442

text to date
 

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 <atyahoo<dotcom
----
"Donald" wrote in message
...
Sorry, this was not a code question, rather a worksheet formula question.
Maybe there is a more appropriate forum?

--
Donald
"Donald" wrote in message
...
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

text to date
 

Thanks, looks like BOTH methods work.

--
Donald
"Max" wrote in message
...
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 <atyahoo<dotcom
----
"Donald" wrote in message
...
Sorry, this was not a code question, rather a worksheet formula question.
Maybe there is a more appropriate forum?

--
Donald
"Donald" wrote in message
...
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

text to date
 

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <atyahoo<dotcom
---
"Donald" wrote in message
...
Thanks, looks like BOTH methods work.