| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help
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.newusers
|
|||
|
|||
|
Help
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.newusers
|
|||
|
|||
|
Help
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.newusers
|
|||
|
|||
|
Help
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.newusers
|
|||
|
|||
|
Help
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
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 |
|
#7
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 |
|
#8
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 |
|
#9
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 |
|
#10
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 |
|
#11
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
I entered this in J43 with the values provided by you in O45 (1/10/2008 14:00
) and M 46 (2/10/2008 12:00) =((MIN($K$40,O45-ROUND(O45,0)+1)) - (MAX($K$39,M46-ROUND(M46,0)+1)) + 1)*24 and copied to J44 after copying O45 to O46 and M46 to M47 to get this =((MIN($K$40,O46-ROUND(O46,0)+1)) - (MAX($K$39,M47-ROUND(M47,0)+1)) + 1)*24 I am getting 10 in both cases. See what is different at your end. I tested both with Excell 2003 and 2007 on Windows XP. Pl. note that 10 is decimal 10 and not 10:00 hours... since we have multiplied the result with 24 I can send the sheet I have if you can send me an email. To get my email id paste the following in an Excel cell and press Enter... ="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" -- Always provide your feedback... "Dave08" wrote: Seemsto be a problem. its returns a new value. 9 instead of 10 |
|
#13
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
I have found the issue, the result it is displaying is the hours from 08:00.
THANKS FOR HELP:-) "Sheeloo" wrote: I entered this in J43 with the values provided by you in O45 (1/10/2008 14:00 ) and M 46 (2/10/2008 12:00) =((MIN($K$40,O45-ROUND(O45,0)+1)) - (MAX($K$39,M46-ROUND(M46,0)+1)) + 1)*24 and copied to J44 after copying O45 to O46 and M46 to M47 to get this =((MIN($K$40,O46-ROUND(O46,0)+1)) - (MAX($K$39,M47-ROUND(M47,0)+1)) + 1)*24 I am getting 10 in both cases. See what is different at your end. I tested both with Excell 2003 and 2007 on Windows XP. Pl. note that 10 is decimal 10 and not 10:00 hours... since we have multiplied the result with 24 I can send the sheet I have if you can send me an email. To get my email id paste the following in an Excel cell and press Enter... ="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" -- Always provide your feedback... "Dave08" wrote: Seemsto be a problem. its returns a new value. 9 instead of 10 |
|
#14
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
|
Help
=10-((MIN($K$40,O60-ROUND(O60,0)+1)) - (MAX($K$39,M61-ROUND(M61,0)+1)) + 1)*24
This formula worksif the result is<10 "Dave08" wrote: I have found the issue, the result it is displaying is the hours from 08:00. THANKS FOR HELP:-) "Sheeloo" wrote: I entered this in J43 with the values provided by you in O45 (1/10/2008 14:00 ) and M 46 (2/10/2008 12:00) =((MIN($K$40,O45-ROUND(O45,0)+1)) - (MAX($K$39,M46-ROUND(M46,0)+1)) + 1)*24 and copied to J44 after copying O45 to O46 and M46 to M47 to get this =((MIN($K$40,O46-ROUND(O46,0)+1)) - (MAX($K$39,M47-ROUND(M47,0)+1)) + 1)*24 I am getting 10 in both cases. See what is different at your end. I tested both with Excell 2003 and 2007 on Windows XP. Pl. note that 10 is decimal 10 and not 10:00 hours... since we have multiplied the result with 24 I can send the sheet I have if you can send me an email. To get my email id paste the following in an Excel cell and press Enter... ="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" -- Always provide your feedback... "Dave08" wrote: Seemsto be a problem. its returns a new value. 9 instead of 10 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
