Hi all,
Facing a challenge at work coming up with a formula for payroll, would appreciate any help.
To preserve privacy of employees I have used Donald Trump and Hillary Clinton as the names in my example.
In the payroll system we rely on time which is "clocked in" by employees when they sign in and out each day (C:D). They are given rosters and expected to adhere to these (I:J).
So if Donald turns up half an hour late and says traffic was gridlocked, that's ok, Mr Trump will recover 30 minutes at the end of his shift. During this shift Donald may clock out for his lunch break if he has one.
Meanwhile Hillary has arranged to come an hour early to work, because she needs to attend a meeting afterwards so will need to leave an hour early. Also ok, no harm done.
The managers would like a way to track how many hours were worked within the rostered hours (Adherence to shift), as it has become difficult to track with so very many employees joining the company.
A formula in K2 would need to check the following:
- if employee was early then account for only the time after shift begins
- if employee finished past the end of shift, account for only time up until shift ends
I've attached a screenshot of what the raw data looks like, as well as a sample Excel file for tinkering.
Formula in F2 is =SUMIFS(E$1:E2,A$1:A2,A2,B$1:B2,B2)*24
President Hours.JPG
Once the formula shows hours within roster, I will use a pivot to provide the total per person per day, then Donald and Hillary's managers will use this to determine who is the more reliable employee.
Bookmarks