Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date format to Text Date for use in Mail Merge | Excel Discussion (Misc queries) | |||
Excel 2007 text import as text not date | Excel Discussion (Misc queries) | |||
how do i convert text to date (mm/yy text to mm/dd/yyyy date)? | Excel Discussion (Misc queries) | |||
Concatenating a Text and a Date without losing orginal Date Format | Excel Discussion (Misc queries) | |||
Help: How do I convert a text date into a real date format | Excel Worksheet Functions |