Forum Discussion
IF Function - Formula Help
I am trying to create a formula to track hours of service for drivers. The driver is only allowed to work 70 hours continuously. The Driving column and the on duty column 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
6 Replies
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 driver works, on time duty is subtracted from off duty time. If driver has a day off, this amount would be 24.
Hi Sharlene
How about the attached
Thank you for the response. That kind of works, would you be able to help me fix the formulas a bit. 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.
- Hi Sharlene
Could you attach an Excel example and type in some dummy numbers and also type in the expected result you want to get from the formula. It would help me to understand how you want it to work
Thanks
Wyn
