I have a column of numbers that I need to convert to dates, such as:

7252003
11152001
3312002

Some have length of 7, and some are 8. I can do this manually by
adding "0" to the 7 length numbers, then Data, Text to Columns/Date.
Is there a way to do this (faster) in a macro?

Thanks,
Marc

Dim rng as Range, cell as Range
set rng = Range(Cells(1,ActiveCell.Column), _
Cells(rows.count,ActiveCell.Column).End(xlup))
for each cell in rng
if len(cell.Text) = 7 then
Cell.Value = "'0" & Format(cell.value,"0000000")
End if
Next

--
Regards,
Tom Ogilvy

"Marc" wrote in message
om...
I have a column of numbers that I need to convert to dates, such as:

7252003
11152001
3312002

Some have length of 7, and some are 8. I can do this manually by
adding "0" to the 7 length numbers, then Data, Text to Columns/Date.
Is there a way to do this (faster) in a macro?

Thanks,
Marc

thanks, Tom- the only problem with this method is speed-
it took me 64 seconds to process 10,000 records; and what
if I had to do 50,000? Does anyone know of a faster
method, possibly using arrays?

Thanks,
Marc



-----Original Message-----
Dim rng as Range, cell as Range
set rng = Range(Cells(1,ActiveCell.Column), _
Cells(rows.count,ActiveCell.Column).End(xlup))
for each cell in rng
if len(cell.Text) = 7 then
Cell.Value = "'0" & Format(cell.value,"0000000")
End if
Next

--
Regards,
Tom Ogilvy

"Marc" wrote in message
. com...
I have a column of numbers that I need to convert to
dates, such as:

7252003
11152001
3312002

Some have length of 7, and some are 8. I can do this
manually by
adding "0" to the 7 length numbers, then Data, Text to
Columns/Date.
Is there a way to do this (faster) in a macro?

Thanks,
Marc


.

Try this:

Option Explicit

Sub ProcDAtes()
Dim rng As Range, cell As Range, i As Long
Dim lngCalc As Long, varr As Variant
Dim sngStart As Single, lngcnt As Long
sngStart = Timer

lngCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Set rng = Range(Cells(1, ActiveCell.Column), _
Cells(Rows.Count, ActiveCell.Column).End(xlUp))
varr = rng
For i = 1 To UBound(varr)
If Len(varr(i, 1)) = 7 Then
lngcnt = lngcnt + 1
varr(i, 1) = "'0" & Format(varr(i, 1), "0000000")
End If
Next
rng = varr
Application.Calculation = lngCalc
Application.ScreenUpdating = True
Debug.Print Timer - sngStart, "rows: " & rng.Rows.Count & " Processed: " &
lngcnt
End Sub

Did 55,000 cells in 1.5 seconds on my machine.

--
Regards,
Tom Ogilvy

"Marc" wrote in message
...
thanks, Tom- the only problem with this method is speed-
it took me 64 seconds to process 10,000 records; and what
if I had to do 50,000? Does anyone know of a faster
method, possibly using arrays?

Thanks,
Marc



-----Original Message-----
Dim rng as Range, cell as Range
set rng = Range(Cells(1,ActiveCell.Column), _
Cells(rows.count,ActiveCell.Column).End(xlup))
for each cell in rng
if len(cell.Text) = 7 then
Cell.Value = "'0" & Format(cell.value,"0000000")
End if
Next

--
Regards,
Tom Ogilvy

"Marc" wrote in message
. com...
I have a column of numbers that I need to convert to
dates, such as:

7252003
11152001
3312002

Some have length of 7, and some are 8. I can do this
manually by
adding "0" to the 7 length numbers, then Data, Text to
Columns/Date.
Is there a way to do this (faster) in a macro?

Thanks,
Marc


.