MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Time taken - Calculation

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

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

Could you please help me on the issue at the earliest.



10 Answers Found

Answer #1    Answered By: Volker Fischer     Answered On: Feb 13

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.

Answer #2    Answered By: Jack Williams     Answered On: Feb 13

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

Dtime = Etime - Stime

Answer #3    Answered By: Victoria Hughes     Answered On: Feb 13

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.

Answer #4    Answered By: Ramond Fischer     Answered On: Feb 13

Goto http://normay.741.com/ and download Personnel Timesheet 08-12-

Answer #5    Answered By: Cedric Sanders     Answered On: Feb 13

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

Answer #6    Answered By: Edjo Chalthoum     Answered On: Feb 13

I think your questions have got to be in the form of questions - maybe a
little more info than just "it's not working" would be helpful.

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?

Answer #7    Answered By: Beau Smith     Answered On: Feb 13

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.

Answer #8    Answered By: Birk Fischer     Answered On: Feb 13

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.

Answer #9    Answered By: Jordan Watson     Answered On: Feb 13

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.

Answer #10    Answered By: Evan Jones     Answered On: Feb 13

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.