need help with macro to convert number to date

[Dagonini]

I did a search on this site and found a macro to convert a number to a
date. So i tried to update it to match my situation. However, I get a
runtime error 13 type mismatch.

I have a column of birthdates that come across as 20050102 and I need
it to read 01/02/2005. This is the macro:

Sub makedate()

Dim NewDate As Date
Dim cell As Range, sStr As String

Set cell = Range("a:a")
NewDate = CDate(Mid(cell, 3, 2) & "/" & Left(cell, 2) & "/" &
Right(cell, 2))
sStr = Format(NewDate, "dd/mm/yyyy")

End Sub


This is the line that is giving the run time error: NewDate =
CDate(Mid(cell, 3, 2) & "/" & Left(cell, 2) & "/" & Right(cell, 2))

Does anyone have an idea of why?

 

[Bob Umlas]

Problem is that cell is the entire column A! and you can't take the Mid of
it!
set Cell = Range("A1") could work.
But you don't need a macro. there's a builtin feature to do what you want
Select the date(s)
Data/Text-to-Columns
click next twice to get to step 3
Click that it's a Date, and set the format to mdy, then click finish.

Bob Umlas
Excel MVP

 

[Dagonini]

I need to do this automatically. ok. i can mess with it some more.
I've been trying the text to date wizard and it isn't working for me.
it is just giving me the year. Aarg