software

Hello everybody,

Schematically, I have the following problem:

A B C D
E
Arrival Go to Lunch Back from Lunch Departure Formula
08:00 12:00 13:00 17:00
normally: (b-a) (d-c)

and then totals for the month at the bottom of the page. Totals for the day
are also presented on the graph (same page). I use 1904 system to avoid the
quot;errorquot; result, getting (at some periods of the day) negative hours...

Basically it's ok, but the graph is quot;skippingquot; from positive to negative
area during the day (when we enter 08:00 at arrival, the total ((b-a) (d-c))
will get negative). Any idea how to deal with that?

F. ex.
if we input only 08:00, the total should say that we have worked 0:00 hours
if we input 08:00 and 12:00, the total should say that we have worked 4:00
hours
if we input 08:00, 12:00 and 13:00 (everything but departure time), the
total should say that we have worked 4:00 hours
if we input 13:00 and 17:00 (working only in afternoon), the total should
say that we have worked 4:00 hours
if we input 08:00, 12:00, 13:00 and 17:00, the total should say that we have
worked 8:00 hours
if we input 08:00 and 16:00 (worked the whole day without the lunch break),
the total should say 8:00 hours

I know that we can always put embedded IF formulas, but it's not very
elegant and could be confusing... Do you have any ideas what is the best
solution for this kind of problem (or at least the best possible IF
statement)?

P.S. I'm not speaking about adding to that some kind of =NOW() formula and
making the totals and graphs indicating working hours with a big precision
and modifying every minute. That would be the best, however I suppose that
it's way too complicated...

Many thanks for any hints and solutions you could have,
Mark
Hi

To avoid the negative times (which can't be right because it can't be done!)
you could use
=MAX((b-a) (d-c),0)

Andy.

quot;markxquot; gt; wrote in message
...
gt; Hello everybody,
gt;
gt; Schematically, I have the following problem:
gt;
gt; A B C D E
gt; Arrival Go to Lunch Back from Lunch Departure Formula
gt; 08:00 12:00 13:00 17:00 normally:
gt; (b-a) (d-c)
gt;
gt; and then totals for the month at the bottom of the page. Totals for the
gt; day are also presented on the graph (same page). I use 1904 system to
gt; avoid the quot;errorquot; result, getting (at some periods of the day) negative
gt; hours...
gt;
gt; Basically it's ok, but the graph is quot;skippingquot; from positive to negative
gt; area during the day (when we enter 08:00 at arrival, the total
gt; ((b-a) (d-c)) will get negative). Any idea how to deal with that?
gt;
gt; F. ex.
gt; if we input only 08:00, the total should say that we have worked 0:00
gt; hours
gt; if we input 08:00 and 12:00, the total should say that we have worked 4:00
gt; hours
gt; if we input 08:00, 12:00 and 13:00 (everything but departure time), the
gt; total should say that we have worked 4:00 hours
gt; if we input 13:00 and 17:00 (working only in afternoon), the total should
gt; say that we have worked 4:00 hours
gt; if we input 08:00, 12:00, 13:00 and 17:00, the total should say that we
gt; have worked 8:00 hours
gt; if we input 08:00 and 16:00 (worked the whole day without the lunch
gt; break), the total should say 8:00 hours
gt;
gt; I know that we can always put embedded IF formulas, but it's not very
gt; elegant and could be confusing... Do you have any ideas what is the best
gt; solution for this kind of problem (or at least the best possible IF
gt; statement)?
gt;
gt; P.S. I'm not speaking about adding to that some kind of =NOW() formula and
gt; making the totals and graphs indicating working hours with a big precision
gt; and modifying every minute. That would be the best, however I suppose that
gt; it's way too complicated...
gt;
gt; Many thanks for any hints and solutions you could have,
gt; Mark
gt;
gt;