time sum incorrect

[mtovbin]

Greetings,

I am building a simplified timesheet with the time portion looking like
this:

Shift Start Shift End Time Worked
14:45 23:30 8.8
17:00 0:00 7.0
12:45 17:00 4.3
0:00 0:30 0.5
6:45 10:15 3.5
15:15 23:30 8.3
7:00 9:00 2.0
18:15 0:00 5.8
0:00 6:30 6.5
7:15 17:30 10.3

The first 2 columns are formatted as time 13:30 and the last column is
formatted as number 1 decimal place.

The formula I use to obtain the hours worked is straightforward
(ShiftEnd-ShiftStart)*24.

When I try and use the sum function to sum up the total time worked per
week I get 56.8 whereas the correct answer is 57. When I just copy the
numbers manually to another column and sum them up, I get the correct
answer. I have a feeling the problem has something to do with the way
Excel stores time but I can't figure it out. Any help appreciated.

Thanks
M. Tovbin

 

[Bob Phillips]

What makes you think it should be 57? I get 56.75, which rounds to 56.8 to 1
decimal place.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[mtovbin]

When you sum up the decimals in Column 3, you get 57. Even if you sum
up just the fractional part, you get a 0.

Best Regards

 

[Bob Phillips]

The point I was making is that you have formatted the result to 1 decimal
place. Format it to 2 decimal places, then add up the decimal and you will
see it comes to 3.75. If you add the decimals when displayed to one place
you do get 4, but it is only display, the real numbers are still 2,3,4, or
whatever decimal places.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[mtovbin]

Gotcha. What you are saying is that it sums up the numbers to the
maximum precision Excel will allow and then rounds up to the 1-st
decimal place. I thought it rounded first and summed up afterwards. So,
what I need to do is round ((ShiftEnd-ShiftStart)*24,1) to get the
"correct" result.

Thanks
MT

 

[Bob Phillips]

No, not quite. It is not rounding it up, it is just displaying it rounded
because the display format is only one decimal place. Also, you are getting
the "correct" result now. Just increase the display to 2 decimal places,
otherwise you are misrepresenting the true time.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[mtovbin]

The "correct" result is always the result that meets some defined
requirements. The requirement in this case is to round the time up to
the nearest tenth of the hour. This is the way my company keeps its
time. Given this requirement, the "correct" result is 57 hours totals
for the week whatever the "true" time may be.

Thanks
MT

 

[Peo Sjoblom]

But you are not rounding up, you round to the nearest 0.1, not up. If you
want to round up you can use

=CEILING((ShiftEnd-ShiftStart)*24,0.1)


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey