Help with calculating overtime hours

kaz123

New Member
Joined
Oct 8, 2017
Messages
31
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
 

Attachments

  • overtime table.png
    overtime table.png
    219 KB · Views: 24
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Please also see table
Employee IDNameDayHoursEmployee IDNameDayHoursSunday hours to be paidTime off in-LieuEmployee IDNameDayHours
1234John SmithSat41234John SmithSat415111234John SmithSun8
1234John SmithSat61234John SmithSat61234John SmithSun10
1234John SmithSat31234John SmithSat31234John SmithSun8
4567Jane BrownSat104567Jane BrownSat7134567Jane BrownSun5
4567Jane BrownSat84567Jane BrownSat54567Jane BrownSun8
4567Jane BrownSat84567Jane BrownSat49999Abby PeterSun9
5555Ritesh PatelSat125555Ritesh PatelSat127777Dan JonesSun27
5555Ritesh PatelSat85555Ritesh PatelSat8
9999Abby PeterSun99
7777Dan JonesSun27324
Original Saturday Overtime HoursFINAL Saturday Overtime HoursSunday Overtime Hours
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,420
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top