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?

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" wrote in message
ups.com...
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?

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


Bob Umlas wrote:
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" wrote in message
ups.com...
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?