Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
About nesting the hour function
I have an worksheet with cell C9=8:30 and D9=17.30 . Then in another cell i
compute the number of hours above 9 that i have been working by =HOUR((D9-C9)-TIME(9,0,0)). For the above combination i get an error #NUM!. If i do the same in 2 steps say D10=(D9-C9)-(TIME(9,0,0)) and D11=HOUR(D10) evrything is fine. Can you tell me why and if there is any way to avoid it? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
About nesting the hour function
On Tue, 24 Jan 2006 06:52:04 -0800, sudeep
wrote: I have an worksheet with cell C9=8:30 and D9=17.30 . Then in another cell i compute the number of hours above 9 that i have been working by =HOUR((D9-C9)-TIME(9,0,0)). For the above combination i get an error #NUM!. If i do the same in 2 steps say D10=(D9-C9)-(TIME(9,0,0)) and D11=HOUR(D10) evrything is fine. Can you tell me why and if there is any way to avoid it? It's a rounding error due to the manner in which Excel (and other SS's) handle the IEEE double precision specifications. If, in D10, instead of entering: =(D9-C9)-(TIME(9,0,0)) you were to enclose that in parentheses: =((D9-C9)-(TIME(9,0,0))) as you would before adding the HOUR function, you would see the same error as that calculation is returning: -5.5511151231257800E-17 The HOUR function then returns an error since the value it is looking at is negative. You can work around it by ROUNDing your result. =HOUR(ROUND(D9-C9-TIME(9,0,0),5)) But there's no real need to use the HOUR function. As a matter of fact, you can probably eliminate both the HOUR and ROUND functions, and also not have to worry about the fact that the HOUR function will only return numbers 0-23 by using: =24*(D9-C9-TIME(9,0,0)) or, if you must have an integer result: =TRUNC(24*(D9-C9-TIME(9,0,0))) The TRUNC function will give a zero for the very small negative numbers that an apparent equality sometimes returns. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
About nesting the hour function
=INT((D9-C9)*24)-9
or =HOUR(24+D9-C9-TIME(9,0,0)) Try these formulas instead. The problem has to with the fact that a formula that has a result of less than 0 can't be converted to a serial code for dates and times. If you take this formula =VALUE(D9-C9-TIME(9,0,0)) you get -5.55111512312578E-17 as a result (because of rounding error) this number has no meaning when thinking in terms of dates and times as far as excel is concerned. "sudeep" wrote: I have an worksheet with cell C9=8:30 and D9=17.30 . Then in another cell i compute the number of hours above 9 that i have been working by =HOUR((D9-C9)-TIME(9,0,0)). For the above combination i get an error #NUM!. If i do the same in 2 steps say D10=(D9-C9)-(TIME(9,0,0)) and D11=HOUR(D10) evrything is fine. Can you tell me why and if there is any way to avoid it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing worksheet cells from within a function | Setting up and Configuration of Excel | |||
Hour() function | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |