How many Hours did an employee work

cameronbenson

New Member
Joined
Jan 29, 2016
Messages
5
Ok, so I have a data set that is extracted from our warehouse management software that shows each pick from stock that an employee does. i have the data dumped into a basic table that records the hour (=hour()) that the employee worked. This way, i can track how many picks the employee did each hour. But here's my problem:

I'm trying to figure out their weekly performance, but the employees bounce around to different tasks throughout the day. There is a goal per hour that they're supposed to hit, but if they spend 3 hours picking and then 2 hours doing put-away, they shouldn't be penalized for the 2 hours they weren't picking. I've been able to make a daily performance workbook, but I'm having trouble making one for the week. Specifically, if they picked 4 hours for the day, their goal for the entire day would be different than that of someone who picked 2 hours for the day.

In short, the daily goal should be based off how many hours each employee picked. In a perfect world, each employee would pick for 8 hours. So if you had 10 employees and your goal per hour was 20, then your daily goal would be 1600. But i need that goal to be flexible based on how many hours each person spent picking.

If possible, i'd like to have the following restrictions to the workbook. I understand if it's not possible.

1. While the report will only be run once a week, it's ok if the report takes some time to run. However, i'd like to make this as simple as possible because not all the Supervisors are a savvy at excel as they pretend to be.
2. The raw data has approx. 21,000 lines (picks) per week so I don't want the formulas to be too large and crash excel (our computers are only running dual cores).
3. I'd like to avoid pivot tables and macros. While I know how to use them and macros can be assigned to a button, you must not underestimate the laziness of some of these people.

This forum has helped me countless times in the past so I look forward to your responses. Unfortunately, I cannot provide the actual report due to security concerns. For ease of formula generation, I will be able to provide the following information:


Employee name is located: Data!Picking_Data[Picker]
Hour the pick was performed: Data!Picking_Data[Picking Hr '#]
Goal per hour is located: Dashboard!C9

Please let me know if you have any questions and I will do what I can to supply more information. I will be here for the next 3.5 hours. I will respond tomorrow if I miss any of your feedback. Again, thank you all so much for your help. :)
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,127,540
Messages
5,625,412
Members
416,101
Latest member
CCoetzee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top