Subtract Time from Time to get time difference

Posted on 2006-11-21
Medium Priority
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 86

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")
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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 2000 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

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

809 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