Convert String to Excel Time in VBA, with specific string format-CodePudding

In VBA, I have two string variables:

var1 = "2022-08-12T10:32:48.363402"
var2 = "2022-08-12T10:32:49.759310"

var1 corresponds to the 12th Aug 2022, time 10:32:48.363402
var2 corresponds to the 12th Aug 2022, time 10:32:49.759310

In VBA, I would like to convert these variables into date format in order to find the time difference between the two, in seconds.

For example, var2-var1 = 10:32:49.759310 - 10:32:48.363402 should return 1.395908 seconds.

I tried using TimeValue(10:32:49.759310) but it does not work as it does not accept the mililseconds, it only works when the seconds value is in integer form, e.g. TimeValue(10:32:49).

CodePudding user response：

You can use this method - also in an International environment:

    Dim Var1        As String
    Dim Var2        As String
    Dim Date1       As Date
    Dim Date2       As Date
    Dim MSeconds1   As Variant
    Dim MSeconds2   As Variant
    Dim Seconds     As Variant
    
    Var1 = "2022-08-12T10:32:48.363402"
    Var2 = "2022-08-12T10:32:49.759310"
    
    Date1 = CDate(Replace(Left(Var1, 19), "T", " "))
    Date2 = CDate(Replace(Left(Var2, 19), "T", " "))
    
    MSeconds1 = CDec(Val(Mid(Var1, 20)))
    MSeconds2 = CDec(Val(Mid(Var2, 20)))
    Seconds = DateDiff("s", Date1, Date2)   CDec(MSeconds2 - MSeconds1)
    
    Debug.Print Seconds

Returns the exact value:

1.395908

CodePudding user response：

Please, try the next function. It returns the date Double value, extracted from string, by adding each its component (Date itself time milliseconds time):

Function strToDateDbl(strD As String) As Double
    Dim arrStr, arrD, arrT, d As Date, dblTime As Double, dblTDecSec As Double
    arrStr = Split(strD, "T"): arrD = Split(arrStr(0), "-")
    d = DateSerial(CLng(arrD(0)), CLng(arrD(1)), CLng(arrD(2)))
    arrT = Split(arrStr(1), ":")
    dblTime = CDbl(CDate(Format(arrT(0) & ":" & arrT(1) & ":" & Int(arrT(2)), "hh:mm:ss"))) 'double of the time part
    dblTDecSec = CDbl((CDbl(arrT(2)) - Int(arrT(2))) / 86400)                                                       'double of milliseconds part
    strToDateDbl = CDbl(d)   dblTime   dblTDecSec
End Function

You can test it with the next sub:

Sub TestStrToDateDbl()
    Dim var1 As String, var2 As String, d1 As Double, d2 As Double
    var1 = "2022-08-12T10:32:48.363402": var2 = "2022-08-12T10:32:49.759310"
    d1 = strToDateDbl(var1)
    d2 = strToDateDbl(var2)
    Debug.Print Round((d2 - d1) * 86400, 6) '86400 number of seconds in a day...
End Sub

Edited:

Please, also test the next a little more compact version (inspired by Gustav code). I thought it is better to choose a solution without risk of erroring because of any localization and also to be more precise. I mean, Excel looks to Round the seconds decimals, not truncate them. Anyhow, the next function will return with reasonable accuracy:

Function strToDateDbl(strD As String) As Double
    Dim arrT, d As Date, dblTDecSec As Double
    d = CDate(Replace(left(strD, 19), "T", " ")): arrT = Split(strD, ":")
    
    dblTDecSec = CDbl((CDbl(arrT(UBound(arrT))) - Int(arrT(UBound(arrT)))) / 86400)    'double of milliseconds part
    strToDateDbl = CDbl(d)   dblTDecSec
End Function