Timesheet Function

[Ricardo]

I have the following in a spreadsheet for working out time work. Everything
adds up into little columns, however when this entry is put in, it produces a
strange result in the over-time column - I know this entry should read as 0,
but for some reason states that it is and exponential value of -15.
Description or Formula in []

27/08/2008
Cell
F83 Start Time 0630 [Start Time - Just a time entry]
F84 Start Lunch 1230 [Start Lunch - Just a time entry]
F85 Finish Lunch 1300 [Finish Lunch - Just a time entry]
F86 Finish Work 1500 [Finish Work - Just a time entry]
F87 Hours Worked 8
[=((F86-F83+(F86<F83))-(F85-F84+(F85<F84)))*24]
F88 Hours Over Time 1.77636E-15 [=MAX(0,F87-$Z$9)] [$Z$9 is a cell stating
that normal working hours are 8]

Any other times are displayed correctly, for example start at 0630, lunch at
1200 till 1230 and finish work at 1500 produces the result of 8 hours worked
and 0 hours over time.

The formula and what not is a result of looking through here and also some
other related websites. Basically i'm after the reason why it displays
1.77636E-15 INSTEAD of 0 for the over time ONLY when the lunch period or 1230
till 1300 is entered on an 8 hour day.

 

[Bob Bridges]

It just so happens I'm a computer contractor and have to deal with this sort
of thing all the time in tracking my own hours. I'm pretty sure your problem
has to do with the way Excel stores hours, minutes and seconds. You may
already be aware that each integer part of the timestamp is the date, and the
fractional part is the time, expressed as a fraction of a day -- that is,
nnnnn.5 is noon, nnnnn.75 is 18:00 and so on. But it's stored in binary
fractions, which cannot store tenths and some other fractions with complete
accuracy (for exactly the same reason 1/3 + 2/3 can come out 0.99999 on your
calculator). So my guess is that after calculating the hours your guy
worked, it came out not exactly to 8 hours but to very slightly more - to
8.00000000000000177636 hours.

The usual way to deal with this is to round your calculated figures to the
nearest quarter hour, half hour, whatever you think appropriate. The MROUND
function is handy for this purpose.

 

[Bob Phillips]

I replicated your data and I got exactly 0. I am wondering if your cells
F83:F86 are formulae, and not calculating exact time periods?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)