VBA convert string to date format

Question

I have a file with strings that are in the format YYMMDD. eg. 120216 (will always be in this format).

I want to convert this into a date ideally in the format DDMMYY or DDMMYYYY.

Has anyone come across a similar problem and how did they resolve it?

If you are going to down vote at least contribute to the thread. FYI the below answer did not work 100% to my scenario I still had to make some adjustments and it involved quite a few little hacks — SeekingAlpha
– SeekingAlpha, Commented Dec 19, 2013 at 5:05
So what code did you try first - its pretty clear why this was downvoted. — brettdj
– brettdj, Commented Dec 19, 2013 at 9:07

Siddharth Rout · Accepted Answer · 2013-12-19 03:51:17Z

4

You can split the string and then use DateSerial. See this example.

Option Explicit

Sub Sample()
    Dim sDate As String
    Dim Y As Long, M As Long, D As Long

    sDate = "120216"
    Y = Left(sDate, 2)
    M = Mid(sDate, 3, 2)
    D = Right(sDate, 2)

    Debug.Print Format(DateSerial(Y, M, D), "DDMMYY")
    Debug.Print Format(DateSerial(Y, M, D), "DDMMYYYY")
End Sub

Followup from comments

Thanks, I am probably going to have to write up a function where I can pass in string dates to get converted. Any chance that the Debug... line be saved into a variable? (can't seem to) or an alternative work around? – Marco Susilo 4 mins ago

I have not done any error handling. I am sure you can take care of that?

Option Explicit

Sub Sample()
    Dim Ret As String

    Ret = GetDate("120216", "DDMMYY")

    Debug.Print Ret

    Ret = GetDate("120216", "DDMMYYYY")

    Debug.Print Ret
End Sub

Function GetDate(sDate As String, sFormat As String) As String
    Dim Y As Long, M As Long, D As Long

    Y = Left(sDate, 2)
    M = Mid(sDate, 3, 2)
    D = Right(sDate, 2)

    GetDate = Format(DateSerial(Y, M, D), sFormat)
End Function

edited Dec 19, 2013 at 3:51

answered Dec 19, 2013 at 3:30

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Siddharth Rout Over a year ago

@andyholaday: Cheers! Fixed it :)

SeekingAlpha Over a year ago

Thanks, I am probably going to have to write up a function where I can pass in string dates to get converted. Any chance that the Debug... line be saved into a variable? (can't seem to) or an alternative work around?

Siddharth Rout Over a year ago

@MarcoSusilo: Yes. See the updated post. You may have to refresh the page.

Collectives™ on Stack Overflow

VBA convert string to date format

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related