Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up or down
I column B I have a start time, in C an end time and in E I have the total
hours. At the end of the month I have a total for column E. If I enter 10:00 in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in column E which is what I want. The problem is at the end of the month it is not adding all the .8's, it is adding 30 days * 45 minutes which is 22.5 rather than 24.0. Is there a formula that I can use to round this up or down. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up or down
10:45 - 10:00 = 0:45
Converted to a decimal = 0.75 So, if you have 0.8 in the cells and 30 of those cells sum to 22.5 then the 0.8 is a FORMATTED value. The true underlying value of those cells is still 0.75. So, to get the cells' true value to be 0.8: =ROUND((C1-B1)*24/0.1,0)*0.1 Then a sum of D1:D30 will return 24. Biff "Kevin" wrote in message ... I column B I have a start time, in C an end time and in E I have the total hours. At the end of the month I have a total for column E. If I enter 10:00 in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in column E which is what I want. The problem is at the end of the month it is not adding all the .8's, it is adding 30 days * 45 minutes which is 22.5 rather than 24.0. Is there a formula that I can use to round this up or down. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up or down
Thanks
Now if I have anouther function I would like to have in the same cell how would I do that? =ROUND((C5-B5)*24/0.1,0)*0.1 and =IF(B5="stat",8,(C5-B5)*24)-D5 "T. Valko" wrote: 10:45 - 10:00 = 0:45 Converted to a decimal = 0.75 So, if you have 0.8 in the cells and 30 of those cells sum to 22.5 then the 0.8 is a FORMATTED value. The true underlying value of those cells is still 0.75. So, to get the cells' true value to be 0.8: =ROUND((C1-B1)*24/0.1,0)*0.1 Then a sum of D1:D30 will return 24. Biff "Kevin" wrote in message ... I column B I have a start time, in C an end time and in E I have the total hours. At the end of the month I have a total for column E. If I enter 10:00 in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in column E which is what I want. The problem is at the end of the month it is not adding all the .8's, it is adding 30 days * 45 minutes which is 22.5 rather than 24.0. Is there a formula that I can use to round this up or down. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up or down
=IF(B5="stat",8,ROUND((C5-B5)*24/0.1,0)*0.1)-D5 ?
You might also look at =IF(B5="stat",8,MROUND((C5-B5)*24,0.1))-D5 if you have the Analysis ToolPak enabled. -- David Biddulph "Kevin" wrote in message ... Thanks Now if I have anouther function I would like to have in the same cell how would I do that? =ROUND((C5-B5)*24/0.1,0)*0.1 and =IF(B5="stat",8,(C5-B5)*24)-D5 "T. Valko" wrote: 10:45 - 10:00 = 0:45 Converted to a decimal = 0.75 So, if you have 0.8 in the cells and 30 of those cells sum to 22.5 then the 0.8 is a FORMATTED value. The true underlying value of those cells is still 0.75. So, to get the cells' true value to be 0.8: =ROUND((C1-B1)*24/0.1,0)*0.1 Then a sum of D1:D30 will return 24. "Kevin" wrote in message ... I column B I have a start time, in C an end time and in E I have the total hours. At the end of the month I have a total for column E. If I enter 10:00 in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in column E which is what I want. The problem is at the end of the month it is not adding all the .8's, it is adding 30 days * 45 minutes which is 22.5 rather than 24.0. Is there a formula that I can use to round this up or down. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up or down
I think u have problem to sum of 30 days working houres. if it is :
1- Insert sum function for working days 2- If sum is not correct: 3- goto Format Cells, Number Page, Custom, in Type EditBox wite : [h]:mm "Kevin" wrote: I column B I have a start time, in C an end time and in E I have the total hours. At the end of the month I have a total for column E. If I enter 10:00 in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in column E which is what I want. The problem is at the end of the month it is not adding all the .8's, it is adding 30 days * 45 minutes which is 22.5 rather than 24.0. Is there a formula that I can use to round this up or down. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I correct rounding errors in Excel formulas? | Excel Worksheet Functions | |||
Rounding to the Nearest Eighth | Excel Discussion (Misc queries) | |||
Rounding | Excel Discussion (Misc queries) | |||
Percentage rounding error in charts | Excel Discussion (Misc queries) | |||
Banker's Rounding - need help! | Excel Discussion (Misc queries) |