Adding Hours Worked Correctly
 

I'm creating a custom timesheet for our company. I have formulas adding the
hours worked in a day as =((D13-C13)+(F13-E13))*24 where D and C are morning
hours and E and F are afternoon hours and the cells are formatted as Time
1:30 PM.

My problem comes about in totaling a week. Right now, my formula of
=SUM(G9:G15) will add 8.17 + 8.17 = 16.33, but it should be 16.34 (the total
cells are format as Number to 2 decimal places).

What can I do to get my formulas to add correctly?

Thanks in advance!

Adding Hours Worked Correctly
 

one or more of your formulas needs to be rounded. the 8.17 is most likely not
8.17, but several decimal places that are only formatted to display 2
decimals.

you could round this way:

=round(((D13-C13)+(F13-E13))*24,2)
--
hope to help,
cm


"CindyJ" wrote:

I'm creating a custom timesheet for our company. I have formulas adding the
hours worked in a day as =((D13-C13)+(F13-E13))*24 where D and C are morning
hours and E and F are afternoon hours and the cells are formatted as Time
1:30 PM.

My problem comes about in totaling a week. Right now, my formula of
=SUM(G9:G15) will add 8.17 + 8.17 = 16.33, but it should be 16.34 (the total
cells are format as Number to 2 decimal places).

What can I do to get my formulas to add correctly?

Thanks in advance!

Adding Hours Worked Correctly
 

Thank you! That seems to be the magic spell I needed - thank you so much!!!!

Cindy

"cm" wrote:

one or more of your formulas needs to be rounded. the 8.17 is most likely not
8.17, but several decimal places that are only formatted to display 2
decimals.

you could round this way:

=round(((D13-C13)+(F13-E13))*24,2)
--
hope to help,
cm


"CindyJ" wrote:

I'm creating a custom timesheet for our company. I have formulas adding the
hours worked in a day as =((D13-C13)+(F13-E13))*24 where D and C are morning
hours and E and F are afternoon hours and the cells are formatted as Time
1:30 PM.

My problem comes about in totaling a week. Right now, my formula of
=SUM(G9:G15) will add 8.17 + 8.17 = 16.33, but it should be 16.34 (the total
cells are format as Number to 2 decimal places).

What can I do to get my formulas to add correctly?

Thanks in advance!

Adding Hours Worked Correctly
 

It looks to me as if it probably *is* adding correctly.
I assume that what you are seeing as 8.17 is 8 hours 10 minutes, or 8 1/6
hours or 8.16666666...
Similarly I guess that what you are seeing as 16.33 is 16 hours 20 minutes,
or 16 1/3 hours or 16.33333333...
You are confusing yourself by displaying the numbers to only 2 decimal
places but assuming that those are full representations of the underlying
numbers; if you display with more decimal places you will get a clearer
idea as to what is going on.
If you want to round the calculations (as distinct from just the display),
either at the final results, or at any of the intermediate stages, or both,
then you can use the ROUND function.
--
David Biddulph

CindyJ wrote:
I'm creating a custom timesheet for our company. I have formulas
adding the hours worked in a day as =((D13-C13)+(F13-E13))*24 where D
and C are morning hours and E and F are afternoon hours and the cells
are formatted as Time 1:30 PM.

My problem comes about in totaling a week. Right now, my formula of
=SUM(G9:G15) will add 8.17 + 8.17 = 16.33, but it should be 16.34
(the total cells are format as Number to 2 decimal places).

What can I do to get my formulas to add correctly?

Thanks in advance!

Adding Hours Worked Correctly
 

On Mon, 2 Nov 2009 12:47:01 -0800, CindyJ
wrote:

I'm creating a custom timesheet for our company. I have formulas adding the
hours worked in a day as =((D13-C13)+(F13-E13))*24 where D and C are morning
hours and E and F are afternoon hours and the cells are formatted as Time
1:30 PM.

My problem comes about in totaling a week. Right now, my formula of
=SUM(G9:G15) will add 8.17 + 8.17 = 16.33, but it should be 16.34 (the total
cells are format as Number to 2 decimal places).

What can I do to get my formulas to add correctly?

Thanks in advance!



The formulas found in the time tracking sheet of this workbook might be
just what you are looking for. They also leave the blank if the value is
zero.

http://office.microsoft.com/en-us/te...33&av=Z XL000