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

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" wrote in message
oups.com...
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

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 wrote:
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" wrote in message
oups.com...
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

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" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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

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 wrote:
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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

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" wrote in message
oups.com...
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 wrote:
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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

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


Bob Phillips wrote:
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" wrote in message
oups.com...
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 wrote:
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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

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


"mtovbin" wrote in message
oups.com...
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


Bob Phillips wrote:
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" wrote in message
oups.com...
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 wrote:
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
oups.com...
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