Change Numbers to Dates

  • Thread starter Thread starter Donnie Stone
  • Start date Start date
D

Donnie Stone

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

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
 
Gord,

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

Regards,
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.
 
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 Copy>Paste Special>Values
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
 
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)
 
I really appreciate everyone's help on this!


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 Copy>Paste Special>Values
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
Click to expand...
 
Back
Top