SQL Server datetime not recognized by VBA's IsDate function

Question

A SQL Server datetime value is represented as 2015-01-29 19:23:00.000 (for example) in a CSV file.

Unfortunately, VBA's IsDate function doesn't recognize this as a valid date when it is evaluated in an Excel macro (I'm guessing because of the fractional seconds). DateValue also fails to convert the value.

Is there a VBA tweak to make this work or do I need to strip the fractional seconds?

** edit **

To further complicate matters, the ActiveCell.Value converts a text value (e.g. 2015-05-20 15:31:30.000) to a decimal (e.g. 42144.4166666667). As such, the Evaluate trick won't work.

To better illustrate the situation, here's the code:

'
' format a CSV file for better readability
'
Sub FormatCSV

  ' move top-left corner
  Range("A2").Select

  ' while the current cell's value is not empty
  Do While Not IsEmpty(ActiveCell)

    ' 2015-05-20 15:31:30.000 => 42144.4166666667
    Debug.Print ActiveCell.Value

    ' if the cell contains a date
    If IsDate(ActiveCell) Then

      ' format entire column as date/time
      ActiveCell.EntireColumn.NumberFormat = "mm/dd/yy hh:mm"

    End If

    ' resize
    ActiveCell.EntireColumn.AutoFit

    ' move a column to the right
    ActiveCell.Offset(0, 1).Select

  Loop

  ' move top-left corner
  Range("A2").Select

End Sub

Answer 1

Seems that VBA does not like the fractional time within the date string.

You can do one of the following:

1. Strip out the fractional time as you already noted.
2. Or you can wrap it with CDate(Evaluate("=DateValue(""2015-01-29 19:23:00.000"")"))

Here is a samble VBA Sub:

Sub Macro1()
    Dim d As String
    Dim v As Variant
    d = "2015-01-29 19:23:00.000"
    v = CDate(Evaluate("=DateValue(""" + d + """)"))
    Debug.Print IsDate(v)
    Debug.Print v
End Sub

This prints out:

True
1/29/2015

Answer 2

same as in @chancea post, but has some difference

Sub test()
    Dim d$, a As Date, b As Date, c As Date, da As Date
    d = "2015-01-29 19:23:00.000"
    a = CDate(DateValue(Split(d, " ")(0)))
    b = CDate(DateValue(Left(d, 10)))
    c = CDate(DateValue(Mid(d, 1, 10)))
    da = DateSerial(Split(d, "-")(0), Split(d, "-")(1), Left(Split(d, "-")(2), 2))
    Debug.Print IsDate(a), IsDate(b), IsDate(c), IsDate(da)
    Debug.Print a, b, c, da
End Sub

output