# Help with calculating overtime hours

1. ## Help with calculating overtime hours

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”) the first shows the number of Saturday overtime and the second show the Sunday hours. 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 attachment

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

overtime table.png

2. ## Re: Help with calculating overtime hours

Please see attached table in ExcelAttachment 738795

4. ## Re: Help with calculating overtime hours

Please see attached the Excel file

5. ## Re: Help with calculating overtime hours

File now attached

6. ## Re: Help with calculating overtime hours

Perhaps the following will help.
1. Paste the following into cell J4 and copy down:
Formula:
`Please Login or Register  to view this content.`

2. Paste the following into cell K4 and copy down:
Formula:
`Please Login or Register  to view this content.`

3. Select cells J4:K13 > select formatting (Ctrl + 1) > select custom > type 0;;; into the window
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)