# Calculating employee overtime

Status
Not open for further replies.

#### kaz123

##### New Member
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 ID Name number of hours Employee ID Name number of hours Employee ID Name Public Holiday Hours Time Off 1234 John Smith 4 1234 John Smith 8 1234 John Smith 15 11 1234 John Smith 6 1234 John Smith 10 4567 Jane Brown 6 7 1234 John Smith 3 1234 John Smith 8 9876 David Hall 0 8 4567 Jane Brown 6 4567 Jane Brown 5 5555 Ritesh Patel 0 20 4567 Jane Brown 7 4567 Jane Brown 8 9999 Abby Peter 0 9 4567 Jane Brown 4 9876 David Hall 8 7777 Dan Jones 3 24 9876 David Hall 8 9999 Abby Peter 9 9876 David Hall 8 7777 Dan Jones 27 5555 Ritesh Patel 12 5555 Ritesh Patel 8 Worksheet1 Worksheet2 Worksheet3

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
Please ignore this as I posted a more recent which is more accurate to what I need

#### Fluff

##### MrExcel MVP, Moderator
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.

Status
Not open for further replies.

Replies
1
Views
145
Replies
3
Views
114
Replies
0
Views
391
Replies
4
Views
260
Replies
9
Views
388

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.

### Which adblocker are you using?

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

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