Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time for some help (Please!)
It's the old time problem.
I want to calculate a persons pick rate. We have Start time, End time and qty picked Times are in dd/mm/yyyy hh:mm:ss format So far I have A2=17/05/2005 06:00:00 'Start time B2= 17/05/2005 07:00:00 'End time C2=B2-A2 'Length of time formatted as dd/hh:mm:ss D2=B2+1*(B2<A2)-A2 'Length of time formatted as number to 5 decimal places D3= 'Qty picked D4=D2*D3*24 A length of time of 0:45 with a pick qty of 90 gives a pick rate of 170 units per hour but if you change the time to read 00:00:30 and a picked qty of 72 the rate should be 8640 units per hour but it does not come to that, where am I going wrong. |
#2
|
|||
|
|||
Not clear why you have the formula that you do in D2. What happens when End
time is less than the start time? Also the rate is usually the number divided by the time. Hence the pick rate should probably involve dividing D3 with D2? Alok Joshi "Dean" wrote: It's the old time problem. I want to calculate a persons pick rate. We have Start time, End time and qty picked Times are in dd/mm/yyyy hh:mm:ss format So far I have A2=17/05/2005 06:00:00 'Start time B2= 17/05/2005 07:00:00 'End time C2=B2-A2 'Length of time formatted as dd/hh:mm:ss D2=B2+1*(B2<A2)-A2 'Length of time formatted as number to 5 decimal places D3= 'Qty picked D4=D2*D3*24 A length of time of 0:45 with a pick qty of 90 gives a pick rate of 170 units per hour but if you change the time to read 00:00:30 and a picked qty of 72 the rate should be 8640 units per hour but it does not come to that, where am I going wrong. |
#3
|
|||
|
|||
Are you saying that pick qty of 72 for 30 minutes should be 8640 per hour?
One hour is 60 minutes so the pick raten per hour should be 144 or did you just explain it incorrectly? To get the qty per hour divide instead use =D3/(D2*24) which of course gives 120 qty per hour if pick rate is 90 for 45 minutes, format as general Regards, Peo Sjoblom "Dean" wrote: It's the old time problem. I want to calculate a persons pick rate. We have Start time, End time and qty picked Times are in dd/mm/yyyy hh:mm:ss format So far I have A2=17/05/2005 06:00:00 'Start time B2= 17/05/2005 07:00:00 'End time C2=B2-A2 'Length of time formatted as dd/hh:mm:ss D2=B2+1*(B2<A2)-A2 'Length of time formatted as number to 5 decimal places D3= 'Qty picked D4=D2*D3*24 A length of time of 0:45 with a pick qty of 90 gives a pick rate of 170 units per hour but if you change the time to read 00:00:30 and a picked qty of 72 the rate should be 8640 units per hour but it does not come to that, where am I going wrong. |
#4
|
|||
|
|||
I tried a couple of things out...
the formula needs to find out how much of a day, in decimal, an hour is, so you divide 1 by 24 for one of your formula components. Also, as has already been mentioned, that formula doesn't do anything, just format the resultant measured time as a number with 5 decimal places. And I get 90 in 45 minutes as 120 per hour... Converted to a vertical layout: start 18/05/2005 6:00:00 end 18/05/2005 6:00:30 time measured 00/00:00:30 measured as your formula?? 0.00035 meas. number formated only (m.a.d.) 0.00035 hours as decimal (h.a.d.) =1/24 0.041666667 multiply factor (Mf) = h.a.d. / m.a.d. 119.9999996 picked during measured time (Pkd) 72 Pick Rate Per Hour ( Pkd * Mf ) 8639.999972 More results: start 18/05/2005 6:00:00 end 18/05/2005 7:00:30 time measured 00/01:00:30 measured as formula?? 0.04201 meas. numb formated only (m.a.d.) 0.04201 hours as decimal (h.a.d.) =1/24 0.041666667 multiply factor (Mf) = h.a.d. / m.a.d. 0.991735537 picked during measured time (Pkd) 72 Pick Rate Per Hour ( Pkd * Mf ) 71.40495868 start 17/05/2005 6:00:00 end 18/05/2005 7:00:30 time measured 01/01:00:30 measured as formula?? 1.04201 meas. numb formated only (m.a.d.) 1.04201 hours as decimal (h.a.d.) =1/24 0.041666667 multiply factor (Mf) = h.a.d. / m.a.d. 0.039986671 picked during measured time (Pkd) 72 Pick Rate Per Hour ( Pkd * Mf ) 2.87904032 start 18/05/2005 6:00:00 end 18/05/2005 6:45:00 time measured 00/00:45:00 measured as formula?? 0.03125 meas. numb formated only (m.a.d.) 0.03125 hours as decimal (h.a.d.) =1/24 0.041666667 multiply factor (Mf) = h.a.d. / m.a.d. 1.333333333 picked during measured time (Pkd) 90 Pick Rate Per Hour ( Pkd * Mf ) 120 Hope we have helped SongBear "Dean" wrote: It's the old time problem. I want to calculate a persons pick rate. We have Start time, End time and qty picked Times are in dd/mm/yyyy hh:mm:ss format So far I have A2=17/05/2005 06:00:00 'Start time B2= 17/05/2005 07:00:00 'End time C2=B2-A2 'Length of time formatted as dd/hh:mm:ss D2=B2+1*(B2<A2)-A2 'Length of time formatted as number to 5 decimal places D3= 'Qty picked D4=D2*D3*24 A length of time of 0:45 with a pick qty of 90 gives a pick rate of 170 units per hour but if you change the time to read 00:00:30 and a picked qty of 72 the rate should be 8640 units per hour but it does not come to that, where am I going wrong. |
#5
|
|||
|
|||
No.
A pick qty of 72 in 30 seconds should give a pick rate of 8640 units per hour. "Peo Sjoblom" wrote: Are you saying that pick qty of 72 for 30 minutes should be 8640 per hour? One hour is 60 minutes so the pick raten per hour should be 144 or did you just explain it incorrectly? To get the qty per hour divide instead use =D3/(D2*24) which of course gives 120 qty per hour if pick rate is 90 for 45 minutes, format as general Regards, Peo Sjoblom "Dean" wrote: It's the old time problem. I want to calculate a persons pick rate. We have Start time, End time and qty picked Times are in dd/mm/yyyy hh:mm:ss format So far I have A2=17/05/2005 06:00:00 'Start time B2= 17/05/2005 07:00:00 'End time C2=B2-A2 'Length of time formatted as dd/hh:mm:ss D2=B2+1*(B2<A2)-A2 'Length of time formatted as number to 5 decimal places D3= 'Qty picked D4=D2*D3*24 A length of time of 0:45 with a pick qty of 90 gives a pick rate of 170 units per hour but if you change the time to read 00:00:30 and a picked qty of 72 the rate should be 8640 units per hour but it does not come to that, where am I going wrong. |
#6
|
|||
|
|||
The only mistake you are making is in the formula:
=D2*D3*24 Change it to: =D3/(D2*24) - Mangesh "Dean" wrote in message ... No. A pick qty of 72 in 30 seconds should give a pick rate of 8640 units per hour. "Peo Sjoblom" wrote: Are you saying that pick qty of 72 for 30 minutes should be 8640 per hour? One hour is 60 minutes so the pick raten per hour should be 144 or did you just explain it incorrectly? To get the qty per hour divide instead use =D3/(D2*24) which of course gives 120 qty per hour if pick rate is 90 for 45 minutes, format as general Regards, Peo Sjoblom "Dean" wrote: It's the old time problem. I want to calculate a persons pick rate. We have Start time, End time and qty picked Times are in dd/mm/yyyy hh:mm:ss format So far I have A2=17/05/2005 06:00:00 'Start time B2= 17/05/2005 07:00:00 'End time C2=B2-A2 'Length of time formatted as dd/hh:mm:ss D2=B2+1*(B2<A2)-A2 'Length of time formatted as number to 5 decimal places D3= 'Qty picked D4=D2*D3*24 A length of time of 0:45 with a pick qty of 90 gives a pick rate of 170 units per hour but if you change the time to read 00:00:30 and a picked qty of 72 the rate should be 8640 units per hour but it does not come to that, where am I going wrong. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time differences in a column | Excel Worksheet Functions | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
Time Sheets | Excel Discussion (Misc queries) | |||
unmet challenge | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |