Convert string to correct date format in VBA

Question

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.

Use Split and DateSerial to rebuild the dates in an unambiguous fashion. — Rory
– Rory, Commented Apr 25, 2016 at 14:51

Ralph · Accepted Answer · 2016-04-25 14:54:27Z

1

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

answered Apr 25, 2016 at 14:54

Ralph

9,4544 gold badges35 silver badges42 bronze badges

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

4 Comments

Trm Over a year ago

Thanks. I guess there is no way around but to create rules for each date format.

Ralph Over a year ago

@Trm: actually, I gave you only a small piece of the function I have essentially created for myself. That function is able to "recognize" and convert and kind of date format incl. . and - and / and any given order YYYYMMDD or MMDDYYYY (US) or YYYYDDMM (RU) etc. to a valid date. Furthermore, it allows to enter numbers only which will also get converted to dates. For example 14 = the date two weeks from now or -30 = the date 30 days ago etc. The function grew over the years. But it is very much worth it and nobody wants a date-picker anymore.

Trm Over a year ago

how do you tell between YYYYMMDD and YYYYDDMM? Asuming month and day is 0-12

Ralph Over a year ago

With . I give a preference towards DD.MM.YYYY with / as separator the preference is towards MM/DD/YYYY. Without any separators (or - as separator) ISO format YYYYMMDD is assumed. If that doesn't work then local computer settings are used (unless overwritten by DB preferences, as I have an SQL backend storing individual user preferences). I used the following as a primary guideline: en.wikipedia.org/wiki/Date_format_by_country

Collectives™ on Stack Overflow

Convert string to correct date format in VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related