Calculate Overtime with 2 codes for the same date

CubaRJ

New Member
Joined
Mar 21, 2022
Messages
29
Office Version
  1. 365
  2. 2019
Platform
  1. 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.

Personnel NumberSanitized NameRoleDepartmentInstallationAtt./Absence typeAtt./abs. type textCCReasonDateHours
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked01-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked02-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked03-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked04-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked05-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked06-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day07-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day08-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day09-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day10-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day11-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day12-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day13-Feb-241.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day13-Feb-248.50
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off14-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off15-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off16-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off17-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off18-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off19-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off20-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day21-Feb-245.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day21-Feb-246.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day22-Feb-245.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day22-Feb-246.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day23-Feb-245.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day23-Feb-246.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day24-Feb-245.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day24-Feb-246.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day25-Feb-245.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day25-Feb-246.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day26-Feb-245.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day26-Feb-246.50
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day27-Feb-246.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8124extra day offsh.night sh.T.OBC7C.GA.11630Double Day27-Feb-248.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked28-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked29-Feb-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked01-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked02-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked03-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8000Attendance offshoreT.OBC7C.GA.11630Embarked04-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship8112extra day offsh.day shiftT.OBC7C.GA.11630Double Day05-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off06-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off07-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off08-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off09-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off10-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off11-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off12-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off13-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off14-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off15-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off16-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship4600OFFSH dayoff - offsh emplDay-off17-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation18-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation19-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation20-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation21-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation22-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation23-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation24-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation25-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation26-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation27-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation28-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation29-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation30-Mar-2412.00
887666Agu, AleProduction OperatorProductionFloating Production Ship1000Annual leaveVacation31-Mar-2412.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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,224,462
Messages
6,178,815
Members
452,881
Latest member
motivationgyan

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