CODEWITHSUNDEEP
excelvba
Trm
Trm

Reputation: 479

Convert string to correct date format in VBA

I have a long list of dates that are in formats:YYYY-MM-DD or DD/MM/YYYY. I use CDate() for conversion. However, if system default date is YYYY-MM-DD, I get wrong dates converted from DD/MM/YYYY where Day and Month gets mixed up if Day is less than 13. For example:

date_string = "12/02/2016"
date_string = Cdate(date_string)
Debug.Print date_string #prints "2016-12-02"

I understand that CDate() largely depends on system and string format. I was wondering if there's a good way to capture day and month and do correct conversion? The list always includes only two mentioned date formats.

Upvotes: 1

Views: 1022

Answers (1)

Ralph
Ralph

Reputation: 9444

As pointed out by @Rory the following function should do the trick:

Function ConvertDate(strTMP As String)

Select Case True
    Case InStr(1, strTMP, "-", vbTextCompare) > 0
        ConvertDate = DateSerial(Split(strTMP, "-")(0), Split(strTMP, "-")(1), Split(strTMP, "-")(2))
    Case InStr(1, strTMP, "/", vbTextCompare) > 0
        ConvertDate = DateSerial(Split(strTMP, "/")(2), Split(strTMP, "/")(1), Split(strTMP, "/")(0))
    Case Else
        ConvertDate = "error"
End Select

End Function

Upvotes: 1

Related Questions