Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding the total hours worked in 5 weeks | Excel Discussion (Misc queries) | |||
Converting Millitary Time to Hours worked and OT hours | Excel Worksheet Functions | |||
wages - multiply hours and minutes by number of hours worked | Excel Discussion (Misc queries) | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
Adding Hours worked | Excel Discussion (Misc queries) |