I am trying to create a formula to track hours of service for drivers. The driver is only allowed to work 70 hours per shift . The Driving column and the On Duty column (Total column includes both amounts) have to be subtracted from HOS column. But if the Off duty column has 36 hours consecutively in a row then the HOS column resets to 70. Please see attachment. Formula for E2 (HOS, Day 2) =IFD2(OffDuty, Day2)>36, then E2 = 70
When the driver works it would need the driving and on duty amounts subtracted from the 70. After 36 consecutive hours of time off the HOS column would reset back to 70. If not consecutive, then it wouldn't reset the 70. The off duty is based on a 24 hour clock, when the driver works the on time duty time is subtracted from off duty time. If driver has a day off, this amount would be 24.
For the Comm. Off Duty column, i still need it to recognize when a driver has been working. So if there is hours worked, the Cumm. Off Duty would be, 24 - hours worked, (highlighted cells in green). Also when the driver has had 36 consecutive hours off, then the HOS column would reset to 70 (highlighted in purple). The Hours Worked column would calculate a cumulative total, until the 36 hours off was reached and then it would reset (highlighted in grey) to zero.
The driver had 36 hours of consecutive time off (G18, G21, G25), so the cell would reset back to 70. Values in cells H19, H21, & H26, would reset to 70. The number highlighted in purple are still following the same formula (HOS - Hours Worked).
