excel - Convert String to Date VBA

Question

I have a String with a date and a time in this format "DD.MM.YYYY HH:mm" which I want to convert to a date (regardless of the format).

I am using this test code, and I can't find why doesn't it work:

Dim Data1 As String, Data2 As Date    
Data1 = "01.01.2015 01:01"
Data2 = CDate(Data1)

Answer 1

Try getting the string into something closer to a conventional EN-US format before converting with CDate.

Dim Data1 As String, Data2 As Date
Data1 = Replace("01.01.2015 01:01", Chr(46), Chr(47))
Data2 = CDate(Data1)
Debug.Print Data2

fwiw, it usually isn't a good idea to provide sample data that produces ambiguous results from MDY and DMY formats. This could be an issue in VBA. Supply data that is definitively one or the other.

For strings containing ambiguous DMY/MDY data like "02.01.2015 01:01", this is a better approach.

Dim Data1 As String, Data2 As Date, vDATE As Variant, vTIME As Variant
Data1 = "02.01.2015 01:01"
vTIME = Split(Data1, Chr(32))
vDATE = Split(vTIME(0), Chr(46))
Data2 = DateSerial(vDATE(2), vDATE(1), vDATE(0)) + TimeValue(vTIME(1))
Debug.Print Data2

VBA makes a 'best guess' using the first method and comes out wrong as 01-Feb-2015. The second method is more definitive and achieves the correct answer of 02-Jan-2015 (assuming a known DMY date format). In short, getting a date isn't always enough; make sure it is the correct date.