CubaRJ
New Member
- Joined
- Mar 21, 2022
- Messages
- 29
- Office Version
- 365
- 2019
- Platform
- Windows
Hello Mr. Excel Community,
I'm seeking assistance with accurately reporting my team's overtime based on an HR report that presents some challenges. The report details hours worked, but employees may record their hours differently, leading to some inconsistencies.
**Business Rule Overview:**
- A standard offshore workday is 12 hours, followed by a 12-hour off period.
- After a 14-day work rotation, employees have a 14-day off period.
- Workdays extending to 15-21 days count as overtime, with up to 7 extra days allowed. These days are paid and suppress an equal number of off days.
- Overtime can be reported as a full 12-hour day, a combination of hours totaling 12, or even less than 12, which is still considered a full extra day for pay purposes.
- Zero-hour entries are to be ignored.
**Reporting Challenge Example:**
Mr. Agu, Alex has reported overtime from 07FEB to 13FEB, which will be compensated. However, he also reported 21FEB-27FEB as double days because he couldn't rest for 14 consecutive days. For my calculations, I should disregard these double days and only count 7 days of overtime.
In his report, Mr. Agu, Alex has entered 13FEB24 twice with different hours (1h and 8.5h), but this should only count as 1 overtime day. Similarly, on 21FEB, he reported 6.5h and 5.5h, which I will count as 1 overtime day.
**What I Need:**
- A method to calculate the actual number of overtime days. For a 21-day stay, only the 7 overtime days should count, not the suppressed off days.
- A way to accurately count days after "Embarked" and ignore entries before "Day-off".
- For the "Reason" column, once "Double Days" is entered, I need to test what follows. If "Day-off" comes next, it's counted; if "Embarked" comes after "Double Day", it's not considered.
I'm looking for a formula or VBA method in Excel that can help streamline this calculation process and ensure that I'm only reporting the correct number of overtime days.
Thank you for your help!
CubaRJ
I'm seeking assistance with accurately reporting my team's overtime based on an HR report that presents some challenges. The report details hours worked, but employees may record their hours differently, leading to some inconsistencies.
**Business Rule Overview:**
- A standard offshore workday is 12 hours, followed by a 12-hour off period.
- After a 14-day work rotation, employees have a 14-day off period.
- Workdays extending to 15-21 days count as overtime, with up to 7 extra days allowed. These days are paid and suppress an equal number of off days.
- Overtime can be reported as a full 12-hour day, a combination of hours totaling 12, or even less than 12, which is still considered a full extra day for pay purposes.
- Zero-hour entries are to be ignored.
**Reporting Challenge Example:**
Mr. Agu, Alex has reported overtime from 07FEB to 13FEB, which will be compensated. However, he also reported 21FEB-27FEB as double days because he couldn't rest for 14 consecutive days. For my calculations, I should disregard these double days and only count 7 days of overtime.
In his report, Mr. Agu, Alex has entered 13FEB24 twice with different hours (1h and 8.5h), but this should only count as 1 overtime day. Similarly, on 21FEB, he reported 6.5h and 5.5h, which I will count as 1 overtime day.
| Personnel Number | Sanitized Name | Role | Department | Installation | Att./Absence type | Att./abs. type text | CC | Reason | Date | Hours |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 01-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 02-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 03-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 04-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 05-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 06-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 07-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 08-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 09-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 10-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 11-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 12-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 13-Feb-24 | 1.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 13-Feb-24 | 8.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 14-Feb-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 15-Feb-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 16-Feb-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 17-Feb-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 18-Feb-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 19-Feb-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 20-Feb-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 21-Feb-24 | 5.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 21-Feb-24 | 6.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 22-Feb-24 | 5.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 22-Feb-24 | 6.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 23-Feb-24 | 5.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 23-Feb-24 | 6.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 24-Feb-24 | 5.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 24-Feb-24 | 6.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 25-Feb-24 | 5.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 25-Feb-24 | 6.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 26-Feb-24 | 5.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 26-Feb-24 | 6.50 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 27-Feb-24 | 6.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8124 | extra day offsh.night sh. | T.OBC7C.GA.11630 | Double Day | 27-Feb-24 | 8.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 28-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 29-Feb-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 01-Mar-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 02-Mar-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 03-Mar-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8000 | Attendance offshore | T.OBC7C.GA.11630 | Embarked | 04-Mar-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 8112 | extra day offsh.day shift | T.OBC7C.GA.11630 | Double Day | 05-Mar-24 | 12.00 |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 06-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 07-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 08-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 09-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 10-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 11-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 12-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 13-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 14-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 15-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 16-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 4600 | OFFSH dayoff - offsh empl | Day-off | 17-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 18-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 19-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 20-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 21-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 22-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 23-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 24-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 25-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 26-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 27-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 28-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 29-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 30-Mar-24 | 12.00 | |
| 887666 | Agu, Ale | Production Operator | Production | Floating Production Ship | 1000 | Annual leave | Vacation | 31-Mar-24 | 12.00 | |
**What I Need:**
- A method to calculate the actual number of overtime days. For a 21-day stay, only the 7 overtime days should count, not the suppressed off days.
- A way to accurately count days after "Embarked" and ignore entries before "Day-off".
- For the "Reason" column, once "Double Days" is entered, I need to test what follows. If "Day-off" comes next, it's counted; if "Embarked" comes after "Double Day", it's not considered.
I'm looking for a formula or VBA method in Excel that can help streamline this calculation process and ensure that I'm only reporting the correct number of overtime days.
Thank you for your help!
CubaRJ
