VBA string with milliseconds to date

Question

I have a string in the form "yyyy-mm-dd hh:mm:ss.mmm" (where the end is milliseconds)

I'd like to convert it to a number, preferably a Date, which preserves all the information

I've tried CDate(), eg.

Dim dateValue As Date
dateValue = CDate("2017-12-23 10:29:15.223")

But get a type mismatch error

Solution 1

A Date type holds the number of days since December 30 1899 with a precision of one second. Though it's still possible to hold the milliseconds by storing the date in a currency type since it can hold 4 extra digits compared to a Date/Double.

So an alternative would be to store the date as a timestamp in a Currency type representing the number of seconds since December 30 1899:

Public Function CDateEx(text As String) As Currency
    Dim parts() As String
    parts = Split(text, ".")
    CDateEx = CCur(CDate(parts(0)) * 86400) + CCur(parts(1) / 1000)
End Function

And to convert the timestamp back to a string:

Public Function FormatDateEx(dt As Currency) As String
    FormatDateEx = Format(dt / 86400, "yyyy-mm-dd HH:mm:ss") & "." & ((dt - Fix(dt)) * 1000)
End Function

Solution 2

Why not use DateAdd to add the last 0.233 seconds after obtaining the whole second as a date Value?

Dim Str As String, MS As String
Dim DateValue As Date
Dim L as Integer
Str = "2017-12-23 10:29:15.223"
For L = 1 to Len(Str)
    If Left(Right(Str, L), 1) = "." Then
        MS = "0" & Right(Str, L)
        Str = Left(Str, Len(Str) - L)
        Exit For
    End If
Next L
DateValue = CDate(Str)
If MS <> "" Then DateValue = DateAdd("S",MS,DateValue)