AS400 Date value conversion to Excel

  • Thread starter Thread starter D.S.
  • Start date Start date
D

D.S.

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.
 
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)
 
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.
 
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.
 
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/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 
Don, same problem I encounter on a regular basis. Gord provided this...

Another solution:
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
Click to expand...
data in column A and insert the formulas.
 
Back
Top