Calculating employee overtime

Status
Not open for further replies.

kaz123

New Member
Joined
Oct 8, 2017
Messages
18
Hello

I wanted some assistance in calculating employee overtime and time-off in a particular way. I have 2 data worksheets, the first shows the number of hours ordinary overtime (Worksheet1) an employee worked and the second show the number of hours they worked during public holiday (Worksheet2). They can have more than one entry and not necessarily do both. I wanted a macro or formula to produce the output on a third worksheet (Worksheet3), one line for each employee, with the following logic

If an employee has less than 24 hours Ordinary Overtime (OO), then the hours from any public holiday overtime (PHO) they did is used to make up the difference, those PHO hours used to make up the difference need go into column Time Off. Any remaining PHO hours will go in column Public Holiday Hours (see John Smith, Jane Brown & David Hall)

If employee only worked OO and not PHO then upto 24 hours will show in column Time Off (see Ritesh Patel)

If employee only worked PHO and not OO then upto 24 hours will show in column Time Off and the rest in column Public Holiday Hours (see Dan Jones & Abby Peter)

Thank you

Ordinary Overtime Hours (OO)Public Holiday Overtime Hours (PHO)Output
Employee IDNamenumber of hoursEmployee IDNamenumber of hoursEmployee IDNamePublic Holiday HoursTime Off
1234John Smith41234John Smith81234John Smith1511
1234John Smith61234John Smith104567Jane Brown67
1234John Smith31234John Smith89876David Hall08
4567Jane Brown64567Jane Brown55555Ritesh Patel020
4567Jane Brown74567Jane Brown89999Abby Peter09
4567Jane Brown49876David Hall87777Dan Jones324
9876David Hall89999Abby Peter9
9876David Hall87777Dan Jones27
5555Ritesh Patel12
5555Ritesh Patel8
Worksheet1Worksheet2Worksheet3
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

kaz123

New Member
Joined
Oct 8, 2017
Messages
18
Please ignore this as I posted a more recent which is more accurate to what I need
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Help with calculating overtime hours

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.
 
Status
Not open for further replies.

Forum statistics

Threads
1,141,060
Messages
5,704,037
Members
421,323
Latest member
Exidous

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
Top