Convert number to date
 

Hi! I am trying to convert a string to a date. however i get Error nr6
"spill".

in cell A1 I have a date written like this "20081012"

Sub anders()
Dim a As Long
a = Range("a1").Value
b = CDate(a)
End Sub


I get the error when trying to convert to date, pls hepl

Convert number to date
 

Maybe

a = Range("a1").Value
mydate = Left(a, 4) & "/" & Mid(a, 3, 2) & "/" & Right(a, 2)

Mike

"Arne Hegefors" wrote:

Hi! I am trying to convert a string to a date. however i get Error nr6
"spill".

in cell A1 I have a date written like this "20081012"

Sub anders()
Dim a As Long
a = Range("a1").Value
b = CDate(a)
End Sub


I get the error when trying to convert to date, pls hepl

Convert number to date
 

in a cell

=DATE(LEFT(A1,4),MID(A1,5,2), MID(A1,7,2))

or VBA

Sub test()
Dim v
Dim dt As Date
ActiveCell = 20081012

v = ActiveCell.Value
dt = DateSerial(Left$(v, 4), Mid(v, 5, 2), Mid(v, 7, 2))
MsgBox dt

End Sub


Regards,
Peter T


"Arne Hegefors" wrote in message
...
Hi! I am trying to convert a string to a date. however i get Error nr6
"spill".

in cell A1 I have a date written like this "20081012"

Sub anders()
Dim a As Long
a = Range("a1").Value
b = CDate(a)
End Sub


I get the error when trying to convert to date, pls hepl

Convert number to date
 

The value 20081012 is not a proper date format, so CDate cannot convert it.
Try this instead (no need for your 'a' variable, you can assign to 'b'
variable directly)...

Sub anders()
b = CDate(Format(Range("A1").Value, "0000-00-00"))
End Sub

--
Rick (MVP - Excel)


"Arne Hegefors" wrote in message
...
Hi! I am trying to convert a string to a date. however i get Error nr6
"spill".

in cell A1 I have a date written like this "20081012"

Sub anders()
Dim a As Long
a = Range("a1").Value
b = CDate(a)
End Sub


I get the error when trying to convert to date, pls hepl