Excel VBA UDF To Handle Milliseconds

[DD646]

Hi,

I have a DateTime String like - 24-8-2017 15:0:0:48 (Where 48 is milliseconds)

I have written a UDF to convert it to an actual DateTImeFormat. This works just fine. However, I am unable to understand how to get the Milliseconds to be cauculated in the UDF.

Any Help will be appreciated??

Function DateTimeConversion(InputDate)

    Dim InputDate As String, DateStr, TimeStr, TimeArray
    
    DateStr = Left(InputDate, Application.WorksheetFunction.Find(" ", InputDate) - 1)
    TimeStr = Trim(Right(InputDate, Len(InputDate) - Application.WorksheetFunction.Find(" ", InputDate)))
    TimeArray = Split(TimeStr, ":")
    
    DateStr = CDate(DateStr)
    TimeStr = TimeValue((TimeArray(0) & ":" & TimeArray(1) & ":" & TimeArray(2)))
    
    
    DateTimeConversion = DateValue(DateStr) + TimeValue(TimeStr)
    
End Function

[BellyGas]

This may turn out to be a silly question but why are you using Strings and Variants rather than Date/Time variables?

[DD646]

Hi,

Thank you for the response.

That is because the Input is a Text String, not an actual date. And TimeArray is a Variant Array

[CK76]

This will work.

Function DateTimeConversion(InputDate As String)
    Dim DateStr, TimeStr, TimeArray
    DateStr = Split(InputDate, " ")(0)
    TimeStr = Split(InputDate, " ")(1)
    TimeArray = Split(TimeStr, ":")
    DateStr = DateValue(DateStr)
    TimeStr = TimeValue(TimeArray(0) & ":" & TimeArray(1) & ":" & TimeArray(2)) + TimeArray(3) / 100 / 24 / 60 / 60
    DateTimeConversion = DateStr + TimeStr
End Function

This will return the datetime value with milliseconds included. Just use appropriate format once it's returned to cell.

0.JPG

NOTE: Due to VBA limitation, Debug.Print will not display milliseconds portion.

[DD646]

Hi,

Thank you for the reply..

I just got it done using this -

Function DateTimeConversion(InputDate As String) As Date

    Dim DateStr As String, TimeStr As String, DateArray, TimeArray, MS As Double
    
    DateStr = Left(InputDate, Application.WorksheetFunction.Find(" ", InputDate) - 1)
    TimeStr = Trim(Right(InputDate, Len(InputDate) - Application.WorksheetFunction.Find(" ", InputDate)))
    TimeArray = Split(TimeStr, ":")
    
    If UBound(TimeArray) > 2 Then
        MS = (TimeArray(3) + 0) / 86400000
    Else
        MS = 0
    End If
    
    DateTimeConversion = DateValue(CDate(DateStr)) + TimeValue(TimeValue((TimeArray(0) & ":" & TimeArray(1) & ":" & TimeArray(2)))) + MS
    
End Function

Thank You. Rep added

[CK76]

Thanks for the rep and sharing solution found

Please mark the thread as solved by using thread tool found at top of your initial post.