Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
Help
You should change the relative references to K39 and K40 to absolute
references as $K$39 and $K$40... since the cells Cell K39 = 22:00:00 Cell K40 = 8:00:00 do not change. By dragging K39 will change to K40 and so on... If this has answered your qeustion then mark it as answered... so that others do not try to provide solutions.. -- Always provide your feedback... "Dave08" wrote: Thanks, I have tried this formula it seems to work. There is a problem when I drag this formula down the coloum, for some reason it stops working, any ideas?? =((MIN($K$40,O45-ROUND(O45,0)+1)) - (MAX($K$39,M46-ROUND(M46,0)+1)) + 1)*24 "Sheeloo" wrote: Try =((MIN(K40,O45-ROUND(O45,0)+1)) - (MAX(K39,M46-ROUND(M46,0)+1)) + 1)*24 -- Always provide your feedback... "Dave08" wrote: That formula will bring back the total hours between the finish and start. I only want the hours between 10pm and 8am. "Sheeloo" wrote: Enter this in any cell =(M46-O45)*24 and format that cell as number You will get the result as 22.00 -- Always provide your feedback... "Dave08" wrote: I am trying to calculate the hours between 22:00 and 08:00. The e.g. below shows a worker finishes work at 14:00pm on 1/10/2008 and starts work at 12:00pm on 2/10/2008 the next day, so their is 10 hours between 22:00(10Pm) and 08:00(8am. The formula below does not seem to bring back the correct result. =IF(M46="","0",MAX($K$40-$M46,0)+MAX($K$39-$O45,0)) Result = 8 should be 10 Cell K39 = 22:00:00 Cell K40 = 8:00:00 Cell O45 = 14:00:00 1/10/2008 Cell M46 = 12:00:00 2/10/2008 |