|
-
July 15th, 2011, 02:09 PM
#1
hh:mm:ss.ms subtraction and addition
I am having the hardest time trying subtract times in this format:
hh2:mm2:ss2.ms2-hh1:mm1:ss1.ms1
The function I am using is this, which I get from somewhere but forgot (it was long time ag0). Recently, I noticed that it was giving wrong results. I tried tweaking it but no success. If someone could tweak it or may be give me another function, that would be really helpful thanks!
Code:
ption Explicit
Const MSecInSec As Double = 1000
Const SecsInMin As Double = 60
Const MinsInHour As Double = 60
Const HoursInDay As Double = 24
Const SecsInDay As Double = HoursInDay * MinsInHour * SecsInMin
Const mSecMult As Double = 1 / (SecsInDay * MSecInSec)
Public Function ReturnTimeDifference(ByVal smalltime As String, ByVal bigtime As String) As String
'On Error GoTo lblerr
Dim TimeA As Date
Dim TimeB As Date
Dim Res As Double
Dim timeparts As Variant
Dim timepartsb As Variant
Dim ms As String
Dim ms2 As String
Dim k As Variant
Dim r As String
Dim s As String
timeparts = Split(smalltime, ":")
timepartsb = Split(bigtime, ":")
ms = timeparts(3)
ms2 = timepartsb(3)
ms = ms & String(2 - Len(ms), "0")
ms2 = ms2 & String(2 - Len(ms2), "0")
TimeA = TimeSerialEx(timeparts(0), timeparts(1), timeparts(2), ms) '01:04:12.2
TimeB = TimeSerialEx(timepartsb(0), timepartsb(1), timepartsb(2), ms2) '05:08:15.55
Res = Format$((TimeB - TimeA) * SecsInDay, "0.0")
'ReturnTimeDifference = Replace(Format$(Res / SecsInDay, "hh:nn:ss:") & _
Round(Res - Fix(Res), 1) * 100, "-", "") 'avoids the decimal point and replaces any - with empty string.
s = Replace(Format$(Res / SecsInDay, "hh:nn:ss:") & _
Round(Res - Fix(Res), 1) * 100, "-", "") 'avoids the decimal point and replaces any - with empty string.
k = Split(s, ":") 'avoids the decimal point
r = k(0) & ":" & k(1) & ":" & k(2)
If k(3) < 10 Then
r = r & ":0" & k(3)
Else
r = r & ":" & k(3)
End If
ReturnTimeDifference = r
End Function
Public Function TimeSerialEx(ByVal inHour As Integer, ByVal inMinute As Integer, ByVal inSecond As Integer, Optional ByVal inMillisecond As Integer = 0) As Date
TimeSerialEx = TimeSerial(inHour, inMinute, inSecond) + (inMillisecond * mSecMult)
End Function
Public Function SecondsToHHMMSS(ByVal secs As Double)
SecondsToHHMMSS = Format(TimeSerial(0, 0, secs), "h:nn:ss")
End Function
'call it now
ReturnTimeDifference("10:20:27:87","10:00:30:98")
'result: wrong calculation.
The error is basically related with the ms part which is really driving me crazy.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|