What formulas should be used?

[excel_111]

Assignment.xlsx

Please help me do this assignment, I am not able to understand what type of formula would be added. I am trying to enter calculations manually but it is taking too long. Please help.

 

[Special-K99]

You could have shortened all that typing to
"Please do my homework for me."

 

[excel_111]

You could have shortened all that typing to
"Please do my homework for me."

I want to do it on my own but I am not able to figure out formula, if you could help me with that. I would be grateful, thanks

 

[Cubist]

What have you tried?

 

[excel_111]

I tried using Index, match, match to try to replicate in every cell but I am not able to figure out calculation along with it.
Manually, I had been using formula as "=(Route!$H5-Route!$E5-(Route!$G5-Route!$F5))*24" for regular work hours and "=+MAX(0,B3-8)" for overtime in every cell by looking up names and copying the values by myself but obviously it is taking too long and I am not even sure if I am doing it right.

 

[Special-K99]

I Think you should be looking at SUMPRODUCT() and possibly a MIN(max hours, 8) and then calculating the number of hours over the 8.

 

[excel_111]

How should I use sumproduct when I have to find time difference between punch out and punch in and then subtract meal timings and make sure that the formula is using values as per employee names and adding up in case a particular employee has punched in multiple times during week

 

[Special-K99]

Use SUMPRODUCT

where employee in Hours = employee in Route
multiplied by ((meal out - punchout) + (meal in - punch in)) - Note double brackets
where week No, in Route equals last character in Regulars Week No. in Hours spreadsheet, use =RIGHT(B$1,1)

This should give you the maximum hours worked per name.

You just have to check for when it is more than hours. Use MIN(number of hours, 8) to produce number of hours of 8 as a minimum.
You could use MAX(number of hours - 8, 0) to produce the overtime.

I think you're looking at SUMPRODUCT in each of those columns B C D E in the Hours sheet.


I managed to get Total Hours worked per person per week (I think) but havent the time to continue with this.

 

[excel_111]

what should i write for employee in hours = employee in route?