Forum Discussion
Calculate hours Percentage of different employees with variable total (long employees list)
Hi all,
Is there anyone who can help me to overcome this issue.
I have a large employees list and I would like to Calculate hours Percentage of different employees with variable total.
| Employee Name | Project | Regular Hours | % | Overtime Hours |
| Abreu, Solange (MZ0180) | ||||
| MZ1603.MZ.0004.FPRH | 152.00 | 0.00 | ||
| MZ1603.MZ.0009.FPRH | 16.00 | 0.00 | ||
| Employee Totals: | 168.00 | 0.00 | ||
| Alfredo, Geremias (MZ0228) | ||||
| MZ1501.MZ.0001.0000.03 | 27.00 | 0.00 | ||
| MZ1501.MZ.0001.0000.04 | 29.00 | 0.00 | ||
| MZ1603.MZ.0009.CORE | 115.00 | 0.00 | ||
| Employee Totals: | 171.00 | 0.00 | ||
| Alfredo, Santos (MZ0122) | ||||
| LEAVE1.SICK | 80.00 | 0.00 | ||
| MZ1603.MZ.0009.CORE | 88.00 | 0.00 | ||
| Employee Totals: | 168.00 | 0.00 | ||
| Antiname, Amisse (MZ0127) | ||||
| MZ1603.MZ.0004.FPRH | 152.00 | 0.00 | ||
| MZ1603.MZ.0009.FPRH | 24.00 | 0.00 | ||
| Employee Totals: | 176.00 | 0.00 | ||
| Armando, Lurdes (MZ0008) | ||||
| MZ1501.MZ.0002.0000.03 | 0.00 | 0.00 | ||
| MZ1501.MZ.0002.0000.04 | 52.00 | 0.00 | ||
| MZ1503.MZ | 116.00 | 0.00 | ||
| Employee Totals: | 168.00 | 0.00 | ||
| Arone, Nilza (MZ0195) | ||||
| SHARED.MZ | 168.00 | 0.00 | ||
| Employee Totals: | 168.00 | 0.00 | ||
| Assumane, Agostinho (MZ0023) | ||||
| LEAVE1.VACA | 8.00 | 0.00 | ||
| MZ1501.MZ.0001.0000.03 | 0.00 | 0.00 | ||
| MZ1501.MZ.0001.0000.04 | 142.00 | 0.00 | ||
| MZ1501.MZ.0002.0000.03 | 0.00 | 0.00 | ||
| MZ1501.MZ.0002.0000.04 | 9.00 | 0.00 | ||
| MZ1701.MZ | 9.00 | 0.00 | ||
| Employee Totals: | 168.00 | 0.00 | ||
| Avelino, Elias (MZ0181) | ||||
| MZ1603.MZ.0004.FPRH | 150.00 | 0.00 | ||
| MZ1603.MZ.0006.FPRH | 16.00 | 0.00 | ||
| MZ1603.MZ.0009.FPRH | 8.00 | 0.00 | ||
| Employee Totals: | 174.00 | 0.00 |
I would appreciate your support,
Thanks,
Please see screenshot for reference. I would try using an Hlookup with a few reference numbers that are set up based on the number of repetitions:
=HLOOKUP(I8,$E$9:$G$10,2,TRUE)
Please see the attached file for reference. It looks like you are receiving some type of standard payroll report. If you have any influence on the report format I have attached the ideal report output that would allow you to easily accomplish your task. If you can get a report in that format then you can use a simple pivot table to get to your end goal. Otherwise, I have attached a few formulas that will work for your current set up:
Please see screenshot for reference. I would try using an Hlookup with a few reference numbers that are set up based on the number of repetitions:
=HLOOKUP(I8,$E$9:$G$10,2,TRUE)
Greetings!
Please find the attached file.
The approach that have taken is
a. Calculate total hours of each project (refer range M4:N17)
b. Calculate % time each employee spends on respective project.
Thanks,
