Time for some help (Please!)

[Dean]

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.

 

[Alok]

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

 

[Peo Sjoblom]

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

 

[SongBear]

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]

No.
A pick qty of 72 in 30 seconds should give a pick rate of 8640 units per hour.

 

[Mangesh]

The only mistake you are making is in the formula:
=D2*D3*24

Change it to:
=D3/(D2*24)


- Mangesh