Working Hours (formula & graph) - any elegant solution?

[markx]

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
"error" result, getting (at some periods of the day) negative hours...

Basically it's ok, but the graph is "skipping" 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

 

[Guest]

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.