Solved

Difference in seconds between two times.

Posted on 2001-06-10
7
757 Views
Last Modified: 2012-06-27
Looking for a solution to finding the difference in seconds between two times in 24 hour format.  Times are given as hhmmss. Example:  Start Time 180000, Stop Time 183000.  Time difference is 30 minutes or 1800 seconds.  Also have situation for Start Time 235959, Stop time 000010 which should be 11 seconds.  Times are never more than 24 hours apart.  I have a temporary solution, but it doesn't seem very elegant.  Any suggestions to make this better and faster.  Occasionally I have 60,000 or 80,000 of these to loop through and calculate.  I made a form with 3 text boxes and a command button to test the code. txtStart and txtStop input the respective times and txtDifference shows result in seconds.    Current code follows:

Private Sub cmdCalculate_Click()
   
    '~~~ Calculate difference between two times
    '~~~ in seconds.  The two times are always presented
    '~~~ as hhmmss, and are NEVER more than 18 hours
    '~~~ apart.  Start time can be any time in 24
    '~~~ hour format between 000000 and 235959.  Stop
    '~~~ time can be from one second after the Start
    '~~~ time to 18 hours after the Start Time.
    '~~~ Calculate the time difference in seconds.
   
       
    Dim StartTime As Date
    Dim StopTime As Date
    Dim Difference As String
    Dim Seconds As Long

'~~~ Convert the hhmmss input into hh:mm:ss format
       
StartTime = CDate(Mid(txtStart.Text, 1, 2) & ":" & Mid(txtStart.Text, 3, 2) & ":" _
& Mid(txtStart.Text, 5, 2))

'~~~ Convert the hhmmss input into hh:mm:ss format
     
StopTime = CDate(Mid(txtStop.Text, 1, 2) & ":" & Mid(txtStop.Text, 3, 2) & ":" _
   & Mid(txtStop.Text, 5, 2))

'~~~ Find difference between StartTime and StopTime
       
Difference = Format(CDbl(StopTime) - CDbl (StoptTime), "HhNnSs")

'~~~ Calculate Seconds from HHMMSS string.
   
Seconds = (Mid(Difference, 1, 2) * 3600) + (Mid(Difference, 3, 2) * 60) _
   + (Mid(Difference, 5, 2))

'~~~ Determine if StopTime was over a midnight.
     
If CDate(StartTime) < CDate(StopTime) Then
   txtDifference.Text = Seconds
Else
   txtDifference.Text = (Seconds - 86400) * -1
End If
   
End Sub
0
Comment
Question by:MitchSavage
7 Comments
 
LVL 27

Expert Comment

by:Ark
Comment Utility
Private Sub Command1_Click()
  Text1 = SecDiff("10/10/01 23:59:30", "11/10/01 00:00:10")
End Sub

Private Function SecDiff(dt1 As Date, dt2 As Date) As Long
  SecDiff = DateDiff("s", dt1, dt2)
End Function

Cheers
0
 
LVL 27

Expert Comment

by:Ark
Comment Utility
PS.
My locale Date format is dd/mm/yy
If you're using US format (mm/dd/yy), just change:

Text1 = SecDiff("10/10/01 23:59:30", "10/11/01 00:00:10")

and you'll receive same result - 40 sec

Cheers
0
 
LVL 27

Expert Comment

by:Ark
Comment Utility
Or, if you don't want use date:

Private Sub Command1_Click()
  Text1 = SecDiff("23:59:30", "00:00:10")
End Sub

Private Function SecDiff(dt1 As Date, dt2 As Date) As Long
  SecDiff = DateDiff("s", dt1, dt2)
  If SecDiff < 0 Then SecDiff = SecDiff + 86400
End Function

Cheers
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 15

Accepted Solution

by:
ameba earned 100 total points
Comment Utility
' Form1 code
Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Sub Form_Click()
    Dim i As Long, tim0 As Long, result As Long
   
    tim0 = GetTickCount ' start timer
    For i = 1 To 20000
        result = Diff1("235959", "000010")
        result = Diff1("100959", "220010")
    Next
    Print (GetTickCount - tim0) / 1000 ' report time

    ' uses Date functions for calculation
    tim0 = GetTickCount ' start timer
    For i = 1 To 20000
        result = Diff2("235959", "000010")
        result = Diff2("100959", "220010")
    Next
    Print (GetTickCount - tim0) / 1000 ' report time
   
    ' uses Longs in calculation - FAST
    tim0 = GetTickCount ' start timer
    For i = 1 To 20000
        result = Diff3("235959", "000010")
        result = Diff3("100959", "220010")
    Next
    Print (GetTickCount - tim0) / 1000 ' report time

    'txtDifference.Text = CStr(Diff3(txtStart.Text, txtStop.Text))
End Sub

' Calculate time difference in seconds
' Input format: "hhnnss"
Private Function Diff3(Time1 As String, Time2 As String) As Long
    Dim tm1 As Long, tm2 As Long
   
    tm1 = 3600 * CLng(Mid(Time1, 1, 2)) + 60 * CLng(Mid(Time1, 3, 2)) _
        + CLng(Mid(Time1, 5, 2))
    tm2 = 3600 * CLng(Mid(Time2, 1, 2)) + 60 * CLng(Mid(Time2, 3, 2)) _
        + CLng(Mid(Time2, 5, 2))
    If tm2 < tm1 Then tm2 = tm2 + 24& * 3600
   
    Diff3 = tm2 - tm1
End Function

' Calculate difference in seconds
'   uses Date functions for calculation
Private Function Diff2(Time1 As String, Time2 As String) As Long
    Dim tm1 As Date, tm2 As Date
   
    tm1 = TimeSerial(Mid(Time1, 1, 2), Mid(Time1, 3, 2), Mid(Time1, 5, 2))
    tm2 = TimeSerial(Mid(Time2, 1, 2), Mid(Time2, 3, 2), Mid(Time2, 5, 2))
    If tm2 < tm1 Then tm2 = DateAdd("d", 1, tm2)
   
    Diff2 = DateDiff("s", tm1, tm2)
End Function

Private Function Diff1(Time1 As String, Time2 As String) As Long
   Dim StartTime As Date
   Dim StopTime As Date
   Dim Difference As String
   Dim Seconds As Long

'~~~ Convert the hhmmss input into hh:mm:ss format
       
StartTime = CDate(Mid(Time1, 1, 2) & ":" & Mid(Time1, 3, 2) & ":" _
& Mid(Time1, 5, 2))

'~~~ Convert the hhmmss input into hh:mm:ss format
     
StopTime = CDate(Mid(Time2, 1, 2) & ":" & Mid(Time2, 3, 2) & ":" _
  & Mid(Time2, 5, 2))

'~~~ Find difference between StartTime and StopTime
     
Difference = Format(CDbl(StopTime) - CDbl(StartTime), "HhNnSs")

'~~~ Calculate Seconds from HHMMSS string.
 
Seconds = (Mid(Difference, 1, 2) * 3600) + (Mid(Difference, 3, 2) * 60) _
  + (Mid(Difference, 5, 2))

'~~~ Determine if StopTime was over a midnight.
     
If CDate(StartTime) < CDate(StopTime) Then
  Diff1 = Seconds
Else
  Diff1 = (Seconds - 86400) * -1
End If
End Function
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi Mitch,

The following code includes 2 functions:
1. Function GetSecondsDiff
2. Function StrToSeconds
and a Test() sub to test the functions mentioned.

The functions are self exlainatory with comments all over, so it whould be easy to understand. Yet, if some more info is needed, then drop a line.

In the Test() Sub, pls feel free to play with the strStartTime and strEndTime constants to check for a different time diff calculations. In addition, Test() gives you an example of how to use the variouse Err_Codes that GetSecondsDiff can return.

To apply the code in your app, all you need to do is:
call GetSecondsDiff (StartTime, EndTime)
where StartTime and EndTime are variables of type String in "hhmmss" format.

--------- Start Code -------------

Option Explicit

Const SECS_IN_HOUR = 3600
Const SECS_IN_MINUTE = 60
Const DISTINCT_START_END_TIMES = 100

Const Err_WRONG_TIME_FORMAT = -10
Const Err_WRONG_HOUR = -20
Const Err_WRONG_MINUTE = -30
Const Err_WRONG_SECOND = -40


Public Function GetSecondsDiff(StartTime As String, EndTime As String) As Long
   
    Const ADD_24_HOURS = 86400 'Seconds per day
   
    Dim numStartTime As Long
    Dim numEndTime As Long
   
    If Len(StartTime) <> 6 Or Len(EndTime) <> 6 Then
        'Wrong time format
        GetSecondsDiff = IIf(Len(StartTime) <> 6, _
                             Err_WRONG_TIME_FORMAT, _
                             Err_WRONG_TIME_FORMAT * DISTINCT_START_END_TIMES)
                             'The * DISTINCT_START_END_TIMES operation is to
                             'distinct Err_WRONG_TIME_FORMAT between StartTime & EndTime
    Else
        'Calculate Time Diff
        numStartTime = StrToSeconds(StartTime)
        numEndTime = StrToSeconds(EndTime)
        If numStartTime < 0 Or numEndTime < 0 Then
            'Error in numStartTime return valuse possible:
            '    Err_WRONG_HOUR
            '    Err_WRONG_MINUTE
            '    Err_WRONG_SECOND
            'Error in numEndTime return valuse possible:
            '    Err_WRONG_HOUR * DISTINCT_START_END_TIMES
            '    Err_WRONG_MINUTE * DISTINCT_START_END_TIMES
            '    Err_WRONG_SECOND * DISTINCT_START_END_TIMES
            '  The * 10 operation is to distinct Errors between StartTime & EndTime
            GetSecondsDiff = IIf(numStartTime < 0, _
                                 numStartTime, _
                                 numEndTime * DISTINCT_START_END_TIMES)
        Else
            If numEndTime < numStartTime Then
                'Add 24 Hrs to EndTime
                numEndTime = numEndTime + ADD_24_HOURS
            End If
            GetSecondsDiff = numEndTime - numStartTime
        End If
    End If
End Function

Function StrToSeconds(strTimeToConvert As String) As Long
   
    Dim h As Long
    Dim m As Long
    Dim s As Long
   
    h = Val(Left(strTimeToConvert, 2))
    m = Val(Mid(strTimeToConvert, 3, 2))
    s = Val(Right(strTimeToConvert, 2))
   
    h = IIf(h > 23 Or h < 0, Err_WRONG_HOUR, h * SECS_IN_HOUR)
    m = IIf(m > 59 Or m < 0, Err_WRONG_MINUTE, m * SECS_IN_MINUTE)
    s = IIf(s > 59 Or s < 0, Err_WRONG_SECOND, s)
   
    If h < 0 Then
        StrToSeconds = h
    ElseIf m < 0 Then
        StrToSeconds = m
    ElseIf s < 0 Then
        StrToSeconds = s
    Else
        StrToSeconds = h + m + s
    End If
   
End Function

Sub test()
   
    Const strStartTime = "235959"
    Const strEndTime = "000002"
   
    Dim SecondsDiff As Long
    Dim MsgBody As String
    Dim MsgTitle As String
    Dim MsgButton As Integer
   
    SecondsDiff = GetSecondsDiff(strStartTime, strEndTime)
   
    Select Case SecondsDiff
        'GOOD Result
        Case Is > 0
            MsgBody = "Time diff for:" & vbCrLf & vbCrLf & _
                      "Start = " & vbTab & strStartTime & vbCrLf & _
                      "End = " & vbTab & strEndTime & vbCrLf & vbCrLf & _
                      "Is:" & vbCrLf & vbCrLf & _
                      SecondsDiff & " Seconds"
            MsgButton = vbOKOnly
            MsgTitle = "Seconds Diff"
         Case Is = 0
            MsgBody = "StartTime = EndTime!" & vbCrLf & vbCrLf & _
                      "Start = " & vbTab & strStartTime & vbCrLf & _
                      "End = " & vbTab & strEndTime & vbCrLf & vbCrLf
            MsgButton = vbOKOnly
            MsgTitle = "Seconds Diff"
       
       'Error handling
       
        'StartTime Error handling
        Case Is = Err_WRONG_TIME_FORMAT
            MsgBody = "StartTime format is wrong!" & vbCrLf & vbCrLf & _
                      "StartTime entered is: " & strStartTime & vbCrLf & _
                      "Please enter time in ""hhmmss"" (Hours, Minutes, Seconds) format"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
        Case Is = Err_WRONG_HOUR
            MsgBody = "Hour for StartTime is wrong!" & vbCrLf & vbCrLf & _
                      "StartTime entered is: " & strStartTime & vbCrLf & _
                      "Please enter hours for StartTime in the range of 00 - 23"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
        Case Is = Err_WRONG_MINUTE
            MsgBody = "Minutes for StartTime is wrong!" & vbCrLf & vbCrLf & _
                      "StartTime entered is: " & strStartTime & vbCrLf & _
                      "Please enter Minutes for StartTime in the range of 00 - 59"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
        Case Is = Err_WRONG_SECOND
            MsgBody = "Seconds for StartTime is wrong!" & vbCrLf & vbCrLf & _
                      "StartTime entered is: " & strStartTime & vbCrLf & _
                      "Please enter Seconds for StartTime in the range of 00 - 59"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
        'Endtime error handling
        Case Is = Err_WRONG_TIME_FORMAT * DISTINCT_START_END_TIMES
            MsgBody = "EndTime format is wrong!" & vbCrLf & vbCrLf & _
                      "EndTime entered is: " & strEndTime & vbCrLf & _
                      "Please enter time in ""hhmmss"" (Hours, Minutes, Seconds) format"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
        Case Is = Err_WRONG_HOUR * DISTINCT_START_END_TIMES
            MsgBody = "Hour for EndTime is wrong!" & vbCrLf & vbCrLf & _
                      "EndTime entered is: " & strEndTime & vbCrLf & _
                      "Please enter hours for EndTime in the range of 00 - 23"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
        Case Is = Err_WRONG_MINUTE * DISTINCT_START_END_TIMES
            MsgBody = "Minutes for EndTime is wrong!" & vbCrLf & vbCrLf & _
                      "EndTime entered is: " & strEndTime & vbCrLf & _
                      "Please enter Minutes for EndTime in the range of 00 - 59"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
        Case Is = Err_WRONG_SECOND * DISTINCT_START_END_TIMES
            MsgBody = "Seconds for EndTime is wrong!" & vbCrLf & vbCrLf & _
                      "EndTime entered is: " & strEndTime & vbCrLf & _
                      "Please enter Seconds for EndTime in the range of 00 - 59"
            MsgButton = vbCritical
            MsgTitle = "Seconds Diff - Error"
    End Select
    MsgBox MsgBody, MsgButton, MsgTitle
End Sub

--------- End Code -------------

Hope this helps,

Nosterdamus
0
 

Author Comment

by:MitchSavage
Comment Utility
ameba,
The diff3 function is exactly what I was looking for.  It returns the right answers from my input data, is probably as fast as possible, and is in fact much more elegant than the way I was doing it.  Thanks alot.
Best Regards
Mitch.....
0
 
LVL 15

Expert Comment

by:ameba
Comment Utility
Thank you!

Small tip:
"Don't use = operator on Date values" http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=296
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now