I routinely import files from an AS400 to excel and the dates appear as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Donnie,

Put this formula in B1

=DATE(2000+MID(A1,2,2),RIGHT(A1,2),MID(A1,4,2))

and copy down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Donnie Stone" wrote in message
...
I routinely import files from an AS400 to excel and the dates appear as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Donnie

Manually enter this in an adjacent column and copy down by double-clicking on
the right-hand bottom corner of the cell.

=DATE(VALUE("20" & MID(A2,2,2)), VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))

Note: I interpreted your needs differently than Bob did so you will get
different results. You be the judge.

If need a macro......

Sub change_dates()
Selection.Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))"
End Sub

Watch for wordwrap.

Select an appropriate range in an adjacent column and run the macro to
replicate the formula down.

Gord Dibben XL2002

On Sun, 2 Nov 2003 17:49:19 -0500, "Donnie Stone"
wrote:

I routinely import files from an AS400 to excel and the dates appear as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Bob,

I tried your solution and 1031031 equals 7/10/2005.

Donnie

"Bob Phillips" wrote in message
...
Donnie,

Put this formula in B1

=DATE(2000+MID(A1,2,2),RIGHT(A1,2),MID(A1,4,2))

and copy down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Donnie Stone" wrote in message
...
I routinely import files from an AS400 to excel and the dates appear as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Try:

=DATE(2000+MID(A1,2,2),MID(A1,4,2),RIGHT(A1,2))

that give me Oct 31, 2003

--
Regards,
Tom Ogilvy

Donnie Stone wrote in message
...
Bob,

I tried your solution and 1031031 equals 7/10/2005.

Donnie

"Bob Phillips" wrote in message
...
Donnie,

Put this formula in B1

=DATE(2000+MID(A1,2,2),RIGHT(A1,2),MID(A1,4,2))

and copy down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Donnie Stone" wrote in message
...
I routinely import files from an AS400 to excel and the dates appear
as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Gord,

I appreciate your recommendation. I'm new to VBA, will you provide an
example to the range you are referring to?

Regards,
Donnie

<Gord Dibben wrote in message
...
Donnie

Manually enter this in an adjacent column and copy down by double-clicking
on
the right-hand bottom corner of the cell.

=DATE(VALUE("20" & MID(A2,2,2)), VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))

Note: I interpreted your needs differently than Bob did so you will get
different results. You be the judge.

If need a macro......

Sub change_dates()
Selection.Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))"
End Sub

Watch for wordwrap.

Select an appropriate range in an adjacent column and run the macro to
replicate the formula down.

Gord Dibben XL2002

On Sun, 2 Nov 2003 17:49:19 -0500, "Donnie Stone"
wrote:

I routinely import files from an AS400 to excel and the dates appear as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Assume you want the formula in D2 to the last filled value in column A

Sub change_dates()
dim rng as Range
set rng = range(cells(2,1),cells(2,1).End(xldown))
rng.offset(3,0).Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), " & _
"VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))"
End Sub

the formula is specific to converting a formula in cell A2 - when filled
down, it adjusts to refer to subsequent rows. But if you want to start in a
different row, you would need to change the formula.

--
Regards,
Tom Ogilvy


Donnie Stone wrote in message
...
Gord,

I appreciate your recommendation. I'm new to VBA, will you provide an
example to the range you are referring to?

Regards,
Donnie

<Gord Dibben wrote in message
...
Donnie

Manually enter this in an adjacent column and copy down by
double-clicking
on
the right-hand bottom corner of the cell.

=DATE(VALUE("20" & MID(A2,2,2)), VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))

Note: I interpreted your needs differently than Bob did so you will get
different results. You be the judge.

If need a macro......

Sub change_dates()
Selection.Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))"
End Sub

Watch for wordwrap.

Select an appropriate range in an adjacent column and run the macro to
replicate the formula down.

Gord Dibben XL2002

On Sun, 2 Nov 2003 17:49:19 -0500, "Donnie Stone"
wrote:

I routinely import files from an AS400 to excel and the dates appear as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Donnie

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

If new to macros you may want to visit David McRitchie's site first.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord


On Sun, 2 Nov 2003 21:11:55 -0500, "Donnie Stone"
wrote:

Gord,

I appreciate your recommendation. I'm new to VBA, will you provide an
example to the range you are referring to?

Regards,
Donnie

<Gord Dibben wrote in message
.. .
Donnie

Manually enter this in an adjacent column and copy down by double-clicking
on
the right-hand bottom corner of the cell.

=DATE(VALUE("20" & MID(A2,2,2)), VALUE(MID(A2,4,2)), VALUE(RIGHT(A2,2)))

Note: I interpreted your needs differently than Bob did so you will get
different results. You be the judge.

If need a macro......

Sub change_dates()
Selection.Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))"
End Sub

Watch for wordwrap.

Select an appropriate range in an adjacent column and run the macro to
replicate the formula down.

Gord Dibben XL2002

On Sun, 2 Nov 2003 17:49:19 -0500, "Donnie Stone"
wrote:

I routinely import files from an AS400 to excel and the dates appear as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Donnie,

Must be date formats, mine is UK I assume yours is US. Try switching it
around

=DATE(2000+MID(A1,2,2),MID(A1,4,2),RIGHT(A1,2))

Unfortunately, I cannot test things like that.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Donnie Stone" wrote in message
...
Bob,

I tried your solution and 1031031 equals 7/10/2005.

Donnie

"Bob Phillips" wrote in message
...
Donnie,

Put this formula in B1

=DATE(2000+MID(A1,2,2),RIGHT(A1,2),MID(A1,4,2))

and copy down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Donnie Stone" wrote in message
...
I routinely import files from an AS400 to excel and the dates appear
as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

I really appreciate everyone's help on this!


<Gord Dibben wrote in message
...
Donnie

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

If new to macros you may want to visit David McRitchie's site first.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord


On Sun, 2 Nov 2003 21:11:55 -0500, "Donnie Stone"
wrote:

Gord,

I appreciate your recommendation. I'm new to VBA, will you provide an
example to the range you are referring to?

Regards,
Donnie

<Gord Dibben wrote in message
.. .
Donnie

Manually enter this in an adjacent column and copy down by
double-clicking
on
the right-hand bottom corner of the cell.

=DATE(VALUE("20" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))

Note: I interpreted your needs differently than Bob did so you will get
different results. You be the judge.

If need a macro......

Sub change_dates()
Selection.Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))"
End Sub

Watch for wordwrap.

Select an appropriate range in an adjacent column and run the macro to
replicate the formula down.

Gord Dibben XL2002

On Sun, 2 Nov 2003 17:49:19 -0500, "Donnie Stone"
wrote:

I routinely import files from an AS400 to excel and the dates appear
as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie

Appreciate the feedback Donnie.

Gord

On Mon, 3 Nov 2003 17:45:22 -0500, "Donnie Stone"
wrote:

I really appreciate everyone's help on this!


<Gord Dibben wrote in message
.. .
Donnie

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

If new to macros you may want to visit David McRitchie's site first.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord


On Sun, 2 Nov 2003 21:11:55 -0500, "Donnie Stone"
wrote:

Gord,

I appreciate your recommendation. I'm new to VBA, will you provide an
example to the range you are referring to?

Regards,
Donnie

<Gord Dibben wrote in message
.. .
Donnie

Manually enter this in an adjacent column and copy down by
double-clicking
on
the right-hand bottom corner of the cell.

=DATE(VALUE("20" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))

Note: I interpreted your needs differently than Bob did so you will get
different results. You be the judge.

If need a macro......

Sub change_dates()
Selection.Formula = _
"=DATE(VALUE(""20"" & MID(A2,2,2)), VALUE(MID(A2,4,2)),
VALUE(RIGHT(A2,2)))"
End Sub

Watch for wordwrap.

Select an appropriate range in an adjacent column and run the macro to
replicate the formula down.

Gord Dibben XL2002

On Sun, 2 Nov 2003 17:49:19 -0500, "Donnie Stone"
wrote:

I routinely import files from an AS400 to excel and the dates appear
as
follows:

A
1 1031102
2 1031103

A1 equals 11/02/03 & A2 equals 11/03/03.

I'm looking for some help with a macro that would convert the dates
automatically.

Thanks,
Donnie






Gord Dibben XL2002