| 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
I have done, still having the problem???
"Sheeloo" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
Pl. paste the formula you are getting in the new row...
Where are the next set of values? After M46 and O45? Should be in M47 and O46... -- Always provide your feedback... "Dave08" wrote: I have done, still having the problem??? "Sheeloo" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
Thats correct, M47 and O46
"Sheeloo" wrote: Pl. paste the formula you are getting in the new row... Where are the next set of values? After M46 and O45? Should be in M47 and O46... -- Always provide your feedback... "Dave08" wrote: I have done, still having the problem??? "Sheeloo" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
You should not get an error. Pl. make sure that M47 and O46 contain a valid
date and time... Just to test copy the values in M46 and O45 to M47 and O46... -- Always provide your feedback... "Dave08" wrote: Thats correct, M47 and O46 "Sheeloo" wrote: Pl. paste the formula you are getting in the new row... Where are the next set of values? After M46 and O45? Should be in M47 and O46... -- Always provide your feedback... "Dave08" wrote: I have done, still having the problem??? "Sheeloo" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
Seemsto be a problem. its returns a new value. 9 instead of 10
"Sheeloo" wrote: You should not get an error. Pl. make sure that M47 and O46 contain a valid date and time... Just to test copy the values in M46 and O45 to M47 and O46... -- Always provide your feedback... "Dave08" wrote: Thats correct, M47 and O46 "Sheeloo" wrote: Pl. paste the formula you are getting in the new row... Where are the next set of values? After M46 and O45? Should be in M47 and O46... -- Always provide your feedback... "Dave08" wrote: I have done, still having the problem??? "Sheeloo" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
![Sheeloo[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
