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
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:
