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.