Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AS400 Date value conversion to Excel
I have an excel query that return dates from an AS400 system, and the date
'as is', is almost useless for my presentation. For instance, today's date 12/3/03, would be returned as 1031203. Any suggestion to convert this to the MMDDYY format above? It would be great if I could add some type of conditional statement to the SQL code in the query, but could also use an Excel worksheet formula in the adjacent column to convert the value. D.S. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AS400 Date value conversion to Excel
How about selecting your range/column and doing Data|Text to columns.
Fixed width (but "do not import" the first character/field) and dmy for the second field (6 characters) "D.S." wrote: I have an excel query that return dates from an AS400 system, and the date 'as is', is almost useless for my presentation. For instance, today's date 12/3/03, would be returned as 1031203. Any suggestion to convert this to the MMDDYY format above? It would be great if I could add some type of conditional statement to the SQL code in the query, but could also use an Excel worksheet formula in the adjacent column to convert the value. D.S. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AS400 Date value conversion to Excel
Thanks, I may give that a try. I kept stumbling through errors and finally
got this to give me the appearance I was looking for. Where column B has the AS400 date value, and column A has the below formula (no text wrap, it's all one continuous formula line in the cell. ColumnA ColumnB 12/03/03 1031203 = MID(19000000+VALUE(R14),5,2) &"/" & RIGHT(19000000+VALUE(R14),2)&"/"&RIGHT(LEFT(19000000+VALUE(R14),4),2) D.S. "Dave Peterson" wrote in message ... How about selecting your range/column and doing Data|Text to columns. Fixed width (but "do not import" the first character/field) and dmy for the second field (6 characters) "D.S." wrote: I have an excel query that return dates from an AS400 system, and the date 'as is', is almost useless for my presentation. For instance, today's date 12/3/03, would be returned as 1031203. Any suggestion to convert this to the MMDDYY format above? It would be great if I could add some type of conditional statement to the SQL code in the query, but could also use an Excel worksheet formula in the adjacent column to convert the value. D.S. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AS400 Date value conversion to Excel
Sorry,
Last post had an error in as much as the formula indicated cell reference of R14. Using columns A and B of row #1, the cell reference should have been < B2 (and not R14) D.S. "Dave Peterson" wrote in message ... How about selecting your range/column and doing Data|Text to columns. Fixed width (but "do not import" the first character/field) and dmy for the second field (6 characters) "D.S." wrote: I have an excel query that return dates from an AS400 system, and the date 'as is', is almost useless for my presentation. For instance, today's date 12/3/03, would be returned as 1031203. Any suggestion to convert this to the MMDDYY format above? It would be great if I could add some type of conditional statement to the SQL code in the query, but could also use an Excel worksheet formula in the adjacent column to convert the value. D.S. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AS400 Date value conversion to Excel
Hi D.S.,
I think Dave's way is best. Assume AS400 formated data is located in Column A, the code below works on my pc as inteded. Sub Test() With Columns(1) .NumberFormatLocal = "@" .TextToColumns Destination:=Range("A1"), _ DataType:=2, FieldInfo:=Array(Array(0, 1), Array(1, 5)) ' .Clear End With End Sub -- Kind Regards Colo /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Colo of 'The Road of The Cell Masters' :) URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ "D.S." wrote in message ... I have an excel query that return dates from an AS400 system, and the date 'as is', is almost useless for my presentation. For instance, today's date 12/3/03, would be returned as 1031203. Any suggestion to convert this to the MMDDYY format above? It would be great if I could add some type of conditional statement to the SQL code in the query, but could also use an Excel worksheet formula in the adjacent column to convert the value. D.S. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AS400 Date value conversion to Excel
Don, same problem I encounter on a regular basis. Gord provided this...
Another solution: <Gord Dibben wrote in message ... The range in an adjacent column would be one which would extend from the top of column B to the bottom of your data in column A. I have used A2 and B2 so's you could have a title row if needed. This re-written macro will select cells in column B down to the end of data in column A and insert the formulas. NOTE: lines 3 and 4 are one long line. After running the macro you can select column B and CopyPaste SpecialValues then delete Column A. Use the macro recorder to record these steps and plug them into Sub change_dates_2() after the Columns(2).Autofit line. Sub change_dates_2() Dim a As Long Range("B2").Formula = "=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))" a = Range("A" & Rows.Count).End(xlUp).Row Range("B2:B" & a).FillDown Columns(2).Autofit End Sub "Colo" wrote in message ... Hi D.S., I think Dave's way is best. Assume AS400 formated data is located in Column A, the code below works on my pc as inteded. Sub Test() With Columns(1) .NumberFormatLocal = "@" .TextToColumns Destination:=Range("A1"), _ DataType:=2, FieldInfo:=Array(Array(0, 1), Array(1, 5)) ' .Clear End With End Sub -- Kind Regards Colo /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Colo of 'The Road of The Cell Masters' :) URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ "D.S." wrote in message ... I have an excel query that return dates from an AS400 system, and the date 'as is', is almost useless for my presentation. For instance, today's date 12/3/03, would be returned as 1031203. Any suggestion to convert this to the MMDDYY format above? It would be great if I could add some type of conditional statement to the SQL code in the query, but could also use an Excel worksheet formula in the adjacent column to convert the value. D.S. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop date conversion in Excel when pasting | Excel Discussion (Misc queries) | |||
Losing date values upon conversion from EXCEL to CSV | Excel Discussion (Misc queries) | |||
Excel date conversion-UK to US | Excel Worksheet Functions | |||
Turning off Date Conversion in Excel | Excel Discussion (Misc queries) | |||
as400 |
Excel Discussion (Misc queries) |