Search:

# Time taken - Calculation

Asked By: Annie    Date: Feb 13    Category: MS Office    Views: 1725

I have a process starting time and ending time, How can we calculate the time
taken for processing.

For example:

Dim Stime, Etime, Dtime,

Private sub Timecal()

Stime = time
application.wait("0:01:30")
Etime = time

Dtime = Time(hour(Stime), minute(Stime), Second(Stime)) - Time(hour(Etime),
minute(Etime), second(Etime))

End sub

The above mentioned statement is not working. Moreover,

Dtime = Stime - Etime

Both are not working.

Share:

What's with the "URGENT"? Your last question to the group was
also "URGENT". When you write "URGENT", you are implying that your
question should be given some kind of priority.

Here, at last, is a question I could answer, but the "URGENT" really
puts me off.

Not sure if this makes sense: when calculating 'time' you typically subtract the
'earlier' (smaller time  'value) number from the 'later' (larger time value)!
try:

Dtime = Etime - Stime

It depends on exactly what you're trying to do - is correct that you want
Dtime = Etime - Stime. You had Etime and Stime reversed. However, now you
might like to know what to do with it. It depends on what you want to do -
if you just want to know the elapsed seconds, and you know it's less than
one minute, you might just use second(Dtime). If there is a possibility of
minutes, you might use minute(Dtime) * 60 + second(Dtime). It's a little
complicated by the fact that the result is a serial time, which is a
fractional amount of a day that has elapsed since midnight.

07.xls

I tried both the ways ....Its not working

I think your questions have got to be in the form of questions - maybe a

If you subtract the earlier time  from the later time, you get the elapsed
time, in a fractional portion of a day. So, for example, instead of 30
seconds, you get 3.47222222222222E-04, which is the portion of a day that 30
seconds is. If you understand that, the next question is what are you trying
to do with the result?

I think we have several things going on here (or not going on).

First, You may be confusing the VBA "Time" function with the
WorkSheetFunction Time()

In VBA, the time  function only returns the current system time.
(which you are using properly with: Stime = time)

But you cannot use: time(hour(stime),minute(stime),second(stime))
to convert the hour,minutes,seconds to a time format, because that is
the WorkSheetFunction version of Time.

Second, your Application.Wait function says to wait until 1:30 in the
morning... I'm not sure that's what you're wanting.

Try: Application.Wait (Now + TimeValue("0:01:30"))
This calculates a time 1 minute, 30 seconds from Now, and tells
the macro to "sleep" until that time is reached.

The function you probably need is called DateDiff.
It calculates the difference between two dates (including time) in
whatever units you're interested in.

Using this criteria, your macro becomes:

Dim Stime,Dtime
Private Sub Timecal()
Stime = Now
Application.Wait (Now + TimeValue("0:01:30"))
Dtime = DateDiff("s", Stime, Now)
MsgBox Dtime & " seconds"
End Sub

which will produce a Message Box that says: "90 seconds"

Now, another option would be to use the "Timer" function.
this function returns the number of seconds since midnight.
It isn't quite as useful if you're starting  a job at 11:55pm and
ending after midnight, but you probably work day shift anyway.

what I often use is:
----------------------------------------------------------
tstart = Timer
...
Lots of code
...
tstop = Timer
TElapsed = tstop - tstart
TMin = 0
TMin = TElapsed \ 60
TSec = TElapsed Mod 60
msg = ""
If (TMin > 0) Then msg = TMin & " mins "
msg = msg & TSec & " sec"
----------------------------------------------------------

Both of these worked for me.

I was expecting you to answer my question.

Its really easy to understand the query. I appreciate others also to resolve the
issue. This would really helpful for everyone.

Another way to use a timer feature with less code is:

StartTime = Now
'...
'Lots of code
'...
StopTime = Now
TElapsed = Format(StopTime - StartTime, "hh:mm:ss")

Which is what I use when I need to see how long a macro is running. Pop it
into a msgbox. I can leave my desk and still get the run time.

My 2 penneth....

For a lot of timing stuff... And if I want a **really** piined down time  I
use the GetTickCount system function as well.

Didn't find what you were looking for? Find more on Time taken - Calculation Or get search suggestion and latest updates.