Timer function

  • Thread starter Thread starter zoot
  • Start date Start date
Z

zoot

Hello All,
I sure hope someone can help me, I just can't seem to figure this out.
I am using the time function to calculate how long an application is open.
It works fine except until the hour passes midnight then it does not
calculate properly anymore. Does anyone know how I can calculate the time
an application is open and keep a running total of time the application is
open, taking into account if it passes the midnight hour. This is what I
have done so far, thanks for your help excuse any convention errors as I am
new at trying to program in VBA




Cells(2, 8) = Time
mytime = Cells(2, 8) - Cells(1, 8) 'time application is open - time
application is closed
Cells(5, 8) = mytime + Cells(5, 8) 'cumulative time application is open
 
If I did this just as worksheet functions, I could do this:

=B1-A1+(A1>B1)
which is just a shorter version of:
=B1-A1+IF(A1>B1,1,0)

This works if you only cross one midnight. If you ever leave your application
on across 2 midnights, you would probably be better off putting the date and
time into those cells.

cells(2,8) = now

(You could still format that cell to show just the time if that's important to
you.)

Then you could just subtract (normally).
 
Zoot,

Without working through your example I assume that you have a problem when
Cells(2,8).Value is smaller than Cells(1,8).Value so that you are trying to
subtract an later time from an earlier time. This is an oft asked question
with regard to cell function when people say that they have the formula:

=H2-H1

to subtract times and it goes wrong when H2 is an earlier time than H1. The
standard answer is to add 1 by adding a TRUE/FALSE test which, when used in
addition, becomes 1/0 thus:

=H2-H1+(H1<H2)

This would make it:

=01:00 -23:00 +(TRUE) which in General format is =0.04167 - 0.9583 +1 which
in turn is 0.0833 or 2 hours in time format.

Now in VBA TRUE is converted to -1 and FALSE to 0 so the answer to you
problem is:

mytime = Cells(2, 8).Value - Cells(1, 8).Value + (Cells(2, 8).Value <
Cells(1, 8).Value) * -1

Remember this is not tested so save your data before trying

HTH

Sandy
 
Back
Top