Convert String to Date

[yeheya]

Could you pls tell me how to convert a string to date in VBA?

strDate="051228" - Input (YYMMDD)

2005-12-28 - Output

I need this in VBA only

Regards,
Yeheya

 

[Andrew Poulsom]

One way:

Sub Test()
    strdate = "051228"
    MsgBox DateSerial(Left(strdate, 2), Mid(strdate, 3, 2), Right(strdate, 2))
End Sub

 

[yeheya]

Thanks for the quick reply.

It worked.

Thanks
Yeheya

 

[BrianB]

Need to bear in mind that a date is basically a number that can be formatted various ways. So if we want it into a cell as a date we use the serial number.

Sub test()
    Dim strDate As String
    Dim MyDate As Date
    '---------------------------
    strDate = "051228"
    MyDate = DateSerial(2000 + CInt(Left(strDate, 2)), _
        CInt(Mid(strDate, 3, 2)), CInt(Right(strDate, 2)))
    Range("A1").Value = MyDate
    MsgBox (Format(MyDate, "yyyy-mm-dd"))
End Sub

 

[Scott Huish]

BrianB:
You don't need to convert the parts to integer to use the values in the DateSerial function.
Also that assumes that it will always be a year in 2000+

The way Andrew has it posted it will use Excel's method of figuring out the century.

 

[BrianB]

You don't need to convert the parts to integer

Experience in answering queries in this forum shows that it is best not to assume exactly how the code is going to be used. It can save further queries.

 

[ianccy]

How about this?

Input - Nov-28-05

Output - 11/28/2005

 

[Norie]

x = DateValue("Nov-28-05")
MsgBox x