Hourly Pick Rate.

Evening All,

I'm after some help with time and pick rate calculations please.

I have a range of pickers that start at 06:00 and finish at 14:30 -- they have a total of a 1 hour break in between.

I have a total figure for each picker of what they have picked - but I need to work out what the hourly rate is -- maybe it's me going mad or that it is getting late, but the couple of formulas I have tried don't seem to work.

Amy and all help gratefully appreciated.

Regards,

Pat.

Think this works

Say
A1 = Start time
B1 = End Time

enter this in C1 =(B1-A1)-TIME(1,0,0)

TIME(1,0,0) deducts one hour for lunch. Cells formatted as Time

Then in

D1 enter amount picked
E1 enter =D1/(C1*24)

VBA Noob

Hi,

Many thanks for that - works like a charm!!!

I now just need to tidy up the spreadsheet when I have empty cells -- i'm trying to use the 'if' command to do this.

C9 is the total picked figure, e.g. 255
D9 is the first pick time, e.g. 06:43
E9 is the last pick time, e.g. 14:18
F9 is the total pick time, e.g. 06:35 (using the formula above minus 1 hour break)
G9 is the Pick rate per hour, e.g. 39 (again using the formula above)

If any on the cells for the first 3 are blank I want to be able to show the last 2 which have the formulas as blank or with a '0' or a '-' or something to that effect.

All I seem to get from the 'Total Pick Rate' cells is a mass of '#######'.

As ever, any and all help gratefully appreciated.

Regards,

Pat.

Hi try

=IF(OR(C9:E9=""),"-",(E9-D9)-TIME(1,0,0))

and

=IF(OR(C9:E9=""),"-",C9/(F9*24))

Entered as a array

Ctrl + Shift + enter

VBA Noob

VBA Noob,

Mant thanks once again -- works a treat!!!

I really ought to brush up my formula skills, I did have a big thick book knocking about somwhere -- in fact I think it was recommended throught his site.

One last explanation and thats me done -- can you just explain how the C9/(F9*24)) works in the =IF(OR(C9:E9=""),"-",C9/(F9*24))
formula please.

I was trying something daft like trying to convert to minutes and then work it out that way -- no wonder I failed!!!

Thanks again,

Pat.

Hi,

C9 =total picked figure divided by F9 = total pick time times 24 (Since times are stored as a fraction of a 24-hour day, multiplying a time by 24 will return the number of hours).

You can find out more about times from the attached link



http://www.cpearson.com/excel/overtime.htm

VBA Noob

Hi,

Actually, I've just come across another scenario!!!!

I have a picker that picked 39 units with a start time of 06:11 and a finish time of 06:36 -- he was assigned to other duties.
As it is still within the hour, how can I work this out effectively?

As ever, all help and assistance most appreciated.

Regards,

Pat.

=IF(OR(C9:E9=""),"-",IF(24*(E9-D9)<1,E9-D9,(E9-D9)-TIME(1,0,0)))

VBA Noob

Hi again,

Thinking about it you would be better to add another cell to enter the Lunch break time as I assume this may be variable. e.g someone could only take a 30min Lunch break etc

VBA Noob