[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Difference in seconds between two times.

Posted on 2001-06-10
7
Medium Priority
?
777 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 28

Expert Comment

by:Ark
ID: 6172900
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 28

Expert Comment

by:Ark
ID: 6172901
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 28

Expert Comment

by:Ark
ID: 6172907
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Accepted Solution

by:
ameba earned 400 total points
ID: 6172988
' 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
ID: 6173206
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
ID: 6173630
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
ID: 6173872
Thank you!

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Suggested Courses

649 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