SQL Server date and time not recognized by VBA IsDate function
The SQL Server value datetime
is represented as 2015-01-29 19:23:00.000
(for example) in a CSV file.
Unfortunately the VBA function IsDate
does not recognize this as a valid date when it is calculated in an Excel macro (I guess because of the fractional seconds). DateValue
also cannot convert the value.
Is there a VBA setting to make this work, or do I need to remove fractional seconds?
** edit **
To complicate matters further, ActiveCell.Value
converts a text value (for example 2015-05-20 15:31:30.000
) to a decimal number (for example 42144.4166666667
). So the trick Evaluate
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 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
source to share
It seems that VBA doesn't like fractional time in date string.
You can do one of the following:
- Divide the fractional time as you have already noted.
- Or you can wrap it up with
CDate(Evaluate("=DateValue(""2015-01-29 19:23:00.000"")"))
Here's an example 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 gives:
True
1/29/2015
source to share
the same as in the post @chancea
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
source to share