Hello
I wanted some help in calculating employee overtime and time-off in lieu. I have 2 data worksheets (“Original Saturday Overtime Hours” & “Sunday Overtime Hours”) showing the number of Saturday and Sunday overtime hours respectively. They can have more than one entry in each and not necessarily have done both Sat and Sun overtime. I wanted a macro or formula to populate columns D and E so that the original Sat table looks like the “Final Saturday Overtime Hours” table with the additional data entered in the first row for each employee (where applicable). Please see uploaded Overtime table.
The logic:
If employee only worked Saturday hours and no Sunday hours then nothing needs to be done (see Ritesh Patel)
If an employee has less than 24 hours Saturday overtime, then the hours from their Sunday overtime is used to make up the difference, those hours used in making up the difference to reach to 24 hours need go into column “Time off in-Lieu”. Any remaining Sunday hours will go in column “Sunday hours to be paid” (see John Smith: did 13 Sat hours therefore used 11 of his Sunday hours to reach 24. 11 goes in column “Time off in-Lieu” and the remainder 15 Sunday hours goes into column “Sunday hours to be paid”)
If an employee has done more than 24 Saturday hours then all his Sunday hours goes into column “Sunday hours to be paid” (see Jane Brown)
If employee only did Sunday hours then add their details at the bottom and the first 24 hours to show in column “Time off in-Lieu” and the remainder in column “Sunday hours to be paid” (see Dan Jones & Abby Peter)
Thank you
I wanted some help in calculating employee overtime and time-off in lieu. I have 2 data worksheets (“Original Saturday Overtime Hours” & “Sunday Overtime Hours”) showing the number of Saturday and Sunday overtime hours respectively. They can have more than one entry in each and not necessarily have done both Sat and Sun overtime. I wanted a macro or formula to populate columns D and E so that the original Sat table looks like the “Final Saturday Overtime Hours” table with the additional data entered in the first row for each employee (where applicable). Please see uploaded Overtime table.
The logic:
If employee only worked Saturday hours and no Sunday hours then nothing needs to be done (see Ritesh Patel)
If an employee has less than 24 hours Saturday overtime, then the hours from their Sunday overtime is used to make up the difference, those hours used in making up the difference to reach to 24 hours need go into column “Time off in-Lieu”. Any remaining Sunday hours will go in column “Sunday hours to be paid” (see John Smith: did 13 Sat hours therefore used 11 of his Sunday hours to reach 24. 11 goes in column “Time off in-Lieu” and the remainder 15 Sunday hours goes into column “Sunday hours to be paid”)
If an employee has done more than 24 Saturday hours then all his Sunday hours goes into column “Sunday hours to be paid” (see Jane Brown)
If employee only did Sunday hours then add their details at the bottom and the first 24 hours to show in column “Time off in-Lieu” and the remainder in column “Sunday hours to be paid” (see Dan Jones & Abby Peter)
Thank you
Attachments
Last edited: