Help with calculating overtime hours

[kaz123]

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

 

[kaz123]

Please also see table

Employee ID	Name	Day	Hours		Employee ID	Name	Day	Hours	Sunday hours to be paid	Time off in-Lieu		Employee ID	Name	Day	Hours
1234	John Smith	Sat	4		1234	John Smith	Sat	4	15	11		1234	John Smith	Sun	8
1234	John Smith	Sat	6		1234	John Smith	Sat	6				1234	John Smith	Sun	10
1234	John Smith	Sat	3		1234	John Smith	Sat	3				1234	John Smith	Sun	8
4567	Jane Brown	Sat	10		4567	Jane Brown	Sat	7	13			4567	Jane Brown	Sun	5
4567	Jane Brown	Sat	8		4567	Jane Brown	Sat	5				4567	Jane Brown	Sun	8
4567	Jane Brown	Sat	8		4567	Jane Brown	Sat	4				9999	Abby Peter	Sun	9
5555	Ritesh Patel	Sat	12		5555	Ritesh Patel	Sat	12				7777	Dan Jones	Sun	27
5555	Ritesh Patel	Sat	8		5555	Ritesh Patel	Sat	8
					9999	Abby Peter	Sun	9		9
					7777	Dan Jones	Sun	27	3	24
Original Saturday Overtime Hours					FINAL Saturday Overtime Hours							Sunday Overtime Hours