Subtract Time from Time to get time difference

Posted on 2006-11-21
Last Modified: 2011-08-18
dim sendtime as string
dim receivedtime as string

sendTime = Time
'function call
receiveTime = Time

I want to get the minutes and seconds that it took to perfrom the function call and be able to save the result. Eventually there would be lots of these time differences saved and I'd like to sum them all up and find the average time it is taking to complete this function call.

I ust get gibberish from everything I try. Lots of points hope someone is out there!
Question by:wilsoada
LVL 35

Expert Comment

ID: 17991067

your problem is that you are using strings to store the time not dates.  This should resolve the error.

Normally I would do this with the timer object for this.  It is generally more precise


Dim startTime as double
Dim endTime as double

startTime = timer
'do code
endTime = timer

msgbox "Your Function Took:- " & (endtime- starttime) & " Seconds"
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 17991145
You can also do:

Private startTime As Date
Private stopTime As Date
Private elapsed As Date

Private Sub cmdStart_Click()
    startTime = Now
    txtStartTime.Text = Format(startTime, "hh:mm:ss am/pm")
End Sub

Private Sub cmdStop_Click()
    stopTime = Now
    txtStopTime.Text = Format(stopTime, "hh:mm:ss am/pm")
    elapsed = stopTime - startTime
    txtElapsedTime.Text = Format(elapsed, "hh:mm:ss")
End Sub
LVL 12

Expert Comment

ID: 17991180
This one uses the DateDiff() function to calculate the duration (time elapsed in seconds)
and then returns the result in hours, minutes, and seconds.

 Dim vStarts
 Dim vFinish
 Dim vTotal

 vStarts = Now()
 ' some function call
 vFinish = Now()

 vTotal = DateDiff("s", vStarts, vFinish)

 MsgBox "Duration: " & Fix(vTotal / 60 / 60) & " hrs, " & _
                                  Fix(vTotal / 60) Mod 60 & " min, " & _
                                  vTotal Mod 60 & " sec")
LVL 18

Expert Comment

ID: 17991322
When you subtrat two times the result is a double which is the fraction of a day. e.g. 0.5 would be 12 hours

Public Sub Test()

    Dim startTime As Double
    Dim endTime As Double
    startTime = Now()
    'do code
    Debug.Assert True
    endTime = Now()

    ShowTime (CDbl(endTime - startTime))

End Sub
Private Sub ShowTime(dTimeTaken As Double)

    Dim iDays As Long
    Dim iHours As Long
    Dim iMinutes As Long
    Dim iSeconds As Long
    Dim dFractionOfASecond As Double
    Dim iThousandthsOfASecond As Double
    iDays = Fix(dtimetake)
    iHours = Fix((dTimeTaken - iDays) * 24)
    iMinutes = Fix((dTimeTaken - (CDbl(iHours) / 24)) * 60 * 24)
    iSeconds = Fix((dTimeTaken - iHours / 24 - (iMinutes / (24 * 60))) * 60 * 60 * 24)
    MsgBox "Time taken" & iDays & " days" & vbNewLine & _
            iHours & " Hours" & vbNewLine & _
            iMinutes & " Minutes" & vbNewLine & _
            iSeconds & " Seconds"

End Sub

Expert Comment

ID: 17992555
I will add my 2 cents-worth here. All stuff above seems correct.

There is however a nasty gotcha that I ran into dealing with Date/Time in a timecard system written in VB.
Because of the internal representation of "Date" data types, where 0.5 = 12pm, 0.75 = 6pm, etc...
it IS VERY possible that you could have 2 variables of date type, that if printed or displayed on screen in the usual format BOTH might look like "11/21/2006 09:33 pm", yet IF compared to one another, may not be equal.

Here is the Exact comment I left in the code, which speaks all for itself:

'   GOD knows why... but for certain dates/times, it is far better off converting the
'       date/time to a STRING, then compare the strings. During testing, when comparing
'       two times, VB said that "5:23:00 PM" was NOT EQUAL to "5:23:00 PM". BOTH fields
'       were defined as DATES. Curious, I did a PRINT DATE1-DATE2 ==> 1.11022302462516E-16
'       which is some damn # so damn small it is rediculous! Hence, compare times in STRINGS

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 13

Expert Comment

ID: 17994715
^ "GOD knows why..."  and so do I:

The Date type is basically a Double value which is a floating point value.  In computers, there is only a limited precision with floating point values--i.e., once you get a certain number of places to the right of the decimal point, computers need to start rounding stuff off.

If you consider that in a Date value, the date is the integer portion, and the time is the decimal portion, and that the decimal portion is represented by the percentage of minutes of the day that have elapsed, it's easy to understand that for some time values you're going to have these rounding errors.

In your example, 0.5 is 12pm.  But 0.5000000001 is also rounded off to 12pm when displaying the time as a string.  So if one of your Date variables contains 0.5 precisely, and another contains 0.500000001, then when you compare the two Date values they are NOT equal, while if you convert them to String they ARE.

That's why I think comparing dates using the DateDiff function is preferable, because they take this precision issue into account.

LVL 13

Expert Comment

ID: 17994729
I forgot to add this note:  If this program is ever going to be running through midnight, using the Timer method will become a problem, because the Timer function returns a Single value representing the number of seconds elapsed since midnight.  So, if you were to start the process at 11:59PM, and then stop it at 12:01AM, then subtract the start time from the stop time, you'll end up with a huge negative number, instead of 120 seconds like you wanted.  This, again, is another reason to use the DateDiff function.
LVL 17

Expert Comment

ID: 18006868
1) You should always use numerics when working with Date or Time becuase it is way faster.

Also automatically handles midnigh problems.

Dim dtStart As Date
Dim dtEnd As Date

dtStart = Now
' Call Function
dtEnd = Now

MsgBox "Time Taken: " + Format(dtEnd-dtStart, "HHH:NN:SS") ' shows hours mins seconds

2) The is a special function which will do this more accurately for very fast functiuon calls use GetTickCoung. To make this easy to use I put into a class module which I decalare globally. The function returns Milliseconds.

Global GF As New zGF

In your sub:

Dim lStart As Long
Dim lEnd As Long

lStart = GF.GetTickCounter()
' Call Function
lEnd As Long

MsgBox "The function took: " +Format((lEnd - lStart)*.001, "0.000")+" Seconds"

Here is an extract form the class general functions (zGF)

Optional Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long
Public Function GetTickCounter() As Long
GetTickCounter = GetTickCount
End Function

LVL 17

Accepted Solution

inthedark earned 500 total points
ID: 18007078
Woops rushing too much

lStart = GF.GetTickCounter
' Call Function
lEnd = GF.GetTickCounter

Author Comment

ID: 18030204
Sorry forgot I had this open inthedark great this worked perfectly

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

919 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

19 Experts available now in Live!

Get 1:1 Help Now