Productivity depending on different type of work

Hey,

This may be a strange question but I need to know if the below is possible and if it is I may need some help with it

So I need to figure out how productive a group of people have been per day.I know how long they've actually had working (excluding breaks etc) so lets say out of 7 hours they were only working for 6 hours, I would need to do this every day so the hours would keep adding up along with how much work they completed so at the end of the month we know their figures.

The confusing part for me is that each type of work has a different target so Work Type 1 has a target of 4:40 hours and Work Type 2 has a target of 2:20 hours etc.

I need to see if it is possible to work out the productivity based on these and if its possible I need to try and get my head around it.

I've attached a little sample that I put together so any help would be appreciated

Try this in Table!F5 and drag down

Formula:

Please Login or Register  to view this content.

Hi José

Thanks for your prompt reply. I've tried that formula, but I guess that isn't taking the actual amount of time they've had working. for example. lets say a full working day is 7 hours and the target for work is 1 an hour. If Employee 1 does 7 within the 7 hours that would be 100% productivity. This is the part I'm having the issue with because unlike having 1 set target per hour, each work type has a different time target

Hi @traxan

Please clarify me. In Table sheet what means WorkTime_1 = 2 for Employee_1? two days but how many hours?

Morning,

On the table sheet, it's Work 'Type' so that is the type of work they're doing and each work type has a different time target set to it which shows on the 'Work Type' Sheet

Hi @traxan

I'm assuming

1) 'Employee 1' are worked 2 hours in 'Work Type 1' in 25-7-2016 and 26-7-2016 (from 'Raw data' sheet)
2) 'Employee 1' are worked 2 hours in 'Work Type 3' in 25-7-2016 and 26-7-2016 (from 'Raw data' sheet)

3) Each employee must work at least {4:40, 2:20, 1:00, 1:10, 3:30, 4:40, 1:30} = 18:50 hours in a week in that 'Work Types' to get 100% of productivity.
4) 'Employee 1' has missing work {2:40, 2:20, 0:00, 1:10, 3:30, 4:40, 1:30}= 15:50
5) Productivity in 2/5 workdays (2 days in a week) is 1-(15:50/18:50)=15,9%
6) Productivity daily is 15,9%/(2/5)=39,8%

If i'm correct, use this formula

Formula:

Please Login or Register  to view this content.

where G2:M2 has =24*TRANSPOSE('Work Type'!B4:B10), B2 has the weeknumber (31) and E2 has the number of days (2) in 'Raw Data' sheet.
See the file

Hi again,

Sorry mate, I think I've messed up my explanation somewhere so I'll try and sort it out now

Table Sheet:

D4:D10 shows how long each empoyee has been working in hours (this is the same information on the 'Schedule' Sheet)

As this report is MTD the hours would keep increasing each time I enter the raw data.

Cells G5:M10 show how many jobs they've completed within that job type. So as per my the example 'Employee 1' completed 2 x 'Work Type 1' & 2 x 'Work Type 3'.

Now as per the 'Work Type' sheet, we know the target to complete 'Work Type 1' is 4 hours and 40 minutes and 'Work Type 3' is 1 hour .

So we know that in total he has worked 37 hours and 30 mins as per cell D5 on 'Table' Sheet. We also know he has done 4 pieces of work which should technically should have took him 11 hours and 20 minutes (as per the target)

So the productivity for him should be really low.

Do you know what I mean now mate ? Sorry once again and thank you for your continued attempts to try and help me

Hi
I have not forgotten you. Only my work prevented me from replying sooner.

I think the solution you are looking for can be found by finding the percentage of hours contributed to complete the types of work and the number of hours actually worked.
This can be calculated with

Formula:

Please Login or Register  to view this content.

This productivity takes no account of the worker may be to complete the work "more hours". So there may be other measure establishing the maximum productivity of that worker. The formula which gives this value can be

Formula:

Please Login or Register  to view this content.

or (better in my point of view)

Formula:

Please Login or Register  to view this content.

See the file.

You sir are a legend, I think it is working. Let me do some tests and I'll get back to you soon. I can't thank you enough for the effort you've put in to this.

Just to confirm it worked, thanks again for your help

You're welcomed.
Thanks for your rep and feedback.