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

Question

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).

I Split the var1 to separate the date (2022-08-12) and time (10:32:48.363402) - both are still in string format using Split(). Then, I used TimeValue("10:32:48.363402") and it does not work, due to the seconds value not being an integer. Haven't found anything else online to try... — HereToLearnAndHelp
– HereToLearnAndHelp, Commented Aug 12, 2022 at 11:50

Gustav · Accepted Answer · 2022-08-12 13:56:21Z

1

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

edited Aug 12, 2022 at 13:56

answered Aug 12, 2022 at 12:45

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FaneDuru Over a year ago

I am not sure that the difference will will be returned with decimals, as OP required... How would you declare the used variables to make them accepting seconds decimals?

HereToLearnAndHelp Over a year ago

This code returns 1s as the difference, not taking into account the milliseconds (i.e. decimals)

Gustav Over a year ago

Yes, sorry. See modified answer, please.

FaneDuru · Accepted Answer · 2022-08-12 13:31:50Z

0

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

edited Aug 12, 2022 at 13:31

answered Aug 12, 2022 at 12:41

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

5 Comments

FaneDuru Over a year ago

@HereToLearnAndHelp I will try posting a more compact version (inspired by the above answer), but it may be returning with some rounding error (the last decimal, probable) because of Excel truncating the seconds...

Gustav Over a year ago

It fails outside the US, because Int ignores the dot because that's our thousand separator, and Format returns a comma as the decimal separator. Thus, this only runs in a US environment.

FaneDuru Over a year ago

@Gustav I tried an adapted function with reasonable accuracy (the six decimal...), because (I think) Excel rounds the seconds decimals, not truncate them... Your solution works well in terms of returning the date, but not fully accurate in terms of decimals... Anyhow, is obviously more compact...

FaneDuru Over a year ago

@HereToLearnAndHelp Glad I could help! Please, try the second version (after Edited:) and see if it is accurate enough. Beside that, I will like to tell you that we here, when somebody answer our question in a convenient way, tick the code left side, in order to make it accepted answer. In this way, somebody else searching for a similar issue will know that the proposed solution works...

HereToLearnAndHelp Over a year ago

@FaneDuru Thanks for your help. I tested both versions and they both return the desired value. Ticked the code on the left. Helpful :)

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related