Time difference in miliseconds 1

[EfratS]

Hello,

I have two cells in excel which I need to find the time difference between them in miliseconds:
a = 2005-07-07 04:38:43.998
b = 2005-07-07 04:38:44.002

The answer should be: 4

How can I find it using vba code? I saw the "datedif" function but the units don't have milisecond option..
In addition, it seems that "Date" variable in vba don't recognize miliseconds!

any ideas?
thanks!

 

[Ed2020]

Will the dates always be on the same day?

Ed Metcalfe.

Please do not feed the trolls.....

 

[rmikesmith]

EfratS,

As you have already discovered, the built-in date/time routines will not handle your situation. This will require some custom functions. I have cobbled something together below. I make no claim that these functions represent the most efficient way to do this. Also, there is virtually no error checking. And, Ed Metcalfe raises a good question. My code ignores the date portion so if your answer to Ed is no, then you would have to account for that as well. Try out the code; I have tested it (minimally) and it seems to do what you want. Put the following into a standard code module:

Type TimeType
  Hours As Integer
  Minutes As Integer
  Seconds As Integer
  Milliseconds As Integer
End Type


Sub CalcTimeDifference()
Dim sTime1 As String
Dim sTime2 As String
Dim aDT As Variant
Dim udtTime1 As TimeType
Dim udtTime2 As TimeType
Dim TimeInterval As Double
Dim sUnits As String
Dim FormatString As String

   sTime1 = ActiveSheet.Cells(1, 2).Value 'Get time 1 from worksheet
   sTime2 = ActiveSheet.Cells(2, 2).Value 'Get time 2 from worksheet
   aDT = Split(sTime1, " ") 'Separate date / time substrings
   udtTime1 = TimeTypeFromString(aDT(UBound(aDT))) 'Get time components from time portion of aDT
   aDT = Split(sTime2, " ")
   udtTime2 = TimeTypeFromString(aDT(UBound(aDT)))
   
   TimeInterval = TimeDifference(udtTime2, udtTime1) 'Compute difference in seconds
   If TimeInterval < 1 Then
     TimeInterval = TimeInterval * 1000
     sUnits = "milliseconds"
     FormatString = "####"
   Else
     sUnits = "seconds"
     FormatString = "##.####"
   End If
   MsgBox "Time Interval (" & sUnits & ") = " & Format(TimeInterval, FormatString)

End Sub


Function TimeTypeFromString(ByVal T As String) As TimeType
' Parses a time string with format HH:MM:SS.nnn
' Returns time components as the user-defined type TimeType (Hours/Minutes/Seconds/Milliseconds)

Dim Pos As Integer
Dim udtTemp As TimeType
Dim Intervals As Variant
Dim NumOfIntervals As Integer

   With udtTemp
     Pos = InStr(1, T, ".", vbBinaryCompare)
     If Pos > 0 Then
       .Milliseconds = CInt(Mid$(T, Pos + 1))
       T = Left$(T, Pos - 1)
     End If
     If Len(T) <> 0 Then
       Intervals = Split(T, ":")
       NumOfIntervals = UBound(Intervals) - LBound(Intervals) + 1
       Select Case NumOfIntervals
       Case 1
         .Seconds = CInt(Intervals(0))
       Case 2
         .Minutes = CInt(Intervals(0))
         .Seconds = CInt(Intervals(1))
       Case 3
         .Hours = CInt(Intervals(0))
         .Minutes = CInt(Intervals(1))
         .Seconds = CInt(Intervals(2))
       End Select
     
     End If
   End With
     
   TimeTypeFromString = udtTemp
  
End Function


Function TimeDifference(ByRef T1 As TimeType, ByRef T2 As TimeType) As Double
' Computes the difference of T1 - T2
' T1 and T2 are user-defined Type TimeType (Hours/Minutes/Seconds/Milliseconds)

Dim Time1 As Double
Dim Time2 As Double

   With T1
     Time1 = .Hours * 60 * 60 + .Minutes * 60 + .Seconds + .Milliseconds / 1000
   End With
   With T2
     Time2 = .Hours * 60 * 60 + .Minutes * 60 + .Seconds + .Milliseconds / 1000
   End With
   TimeDifference = Time1 - Time2
   
End Function

Of course, the CalcTimeDifference sub is for demonstration purposes, showing one way to read in the data, incorporate the custom functions, and output the result.

Regards,
Mike

 

[PCLewis]

You could rely on the associative property of addition and subtraction:

Public Sub test()
  d1 = "2005-07-07 04:38:43.998"
  d2 = "2005-07-07 04:38:44.002"
  
  d1d = CDbl(CDate(Left(d1, 19)))
  d2d = CDbl(CDate(Left(d2, 19)))
  
  d1f = CDbl(Right(d1, 4)) * 1000#
  d2f = CDbl(Right(d2, 4)) * 1000#
  
  Debug.Print
  Debug.Print DateDiff("s", d1d, d2d) * 1000 + (d2f - d1f)

End Sub

 

[Zathras]

Or you could just use the Windows API:

Declare Function GetTickCount Lib "Kernel32" () As Long

Sub Demo()
  MsgBox GetTickCount
End Sub

Retrieve the value at the start of the test and again at the end. The difference in milliseconds is simply the difference between the two values.

 

[EfratS]

First - thank you all for your answers.

Zathras: I don't need to find the time difference between start to end, but between 2 given values from worksheet.

rmikesmith and PCLewis: your ideas seem to be in the right direction, but I have a basic problem in putting the information from the worksheet into a variable.
In the worksheet I formmated the cell to be:
2005-07-07 04:38:43.998
but when I do: sTime = ActiveSheet.Cells(2, 1).Value
I get: 7/26/2005 4:38:24 AM
I tried also: sTime = Format(ActiveSheet.Cells(6, 1).Value, "yyyy-mm-dd hh:mm:ss.000")
but the variable sTime has the miliseconds rounded! (2005-07-07 04:38:43.000)

thanks again!

 

[PCLewis]

If your basic problem was getting the values into variables then I thin you should have stated that clearly. rmikesmith looks to have gone to some effort to write some code for you but you say the "basic problem" is something completely different.

It seemed clear form your post that you knew how read values in a cell and place them into variables since you posted:
"a = 2005-07-07 04:38:43.998
b = 2005-07-07 04:38:44.002"

When I read thse values from VBA I seem to have no trouble getting them in as strings properly.

Public Function readvalue()
  Dim d1 As String
  d1 = Cells(1, 1).Value
  MsgBox d1
End Function

Cell A1 contains:
'2005-07-07 04:38:43.998

I think you need to tell us how you have them entered in the spreadsheet. A good way to do this is to type them into a cell (not copy/paste) and record what you do. In my case, I started with the ' character to inform Excel that the data being entered is text.

 

[EfratS]

dear PCLewis,

This basic problem arouse when I've tried to implement rmikesmith's solution.
I appriciate his effort very much, and his answer is very relevant to my problem - it just that I encounter another problem in the way. That was my intention.

from your kind answer I now understand that what I need to do is putting the ' sign in the beginning of the cell's content. I wasn't aware to this option.

thanks a lot.

 

[rmikesmith]

EfratS,

I didn't explicitly state this in my post, but when I set up my spreadsheet & code, I formatted the date/time entries as Text before entering them in order to prevent Excel from modifying the entry while converting it to a date/time value. Otherwise, I get the same result that you did: 7/26/2005 4:38:24 AM


Hope this helps.
Mike

 

[PCLewis]

It's good that you've found your solutions because that's why we all contribute. It still doesn' explain to me how you had those values in a Cell without either adding the ' or setting the field to Text as rmikesmith did. I genuinely want to know how because it might be some new tidbit of knowledge to be stored away for some future need