Forum Discussion
IF Function - Formula Help
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.
Apologies Sharlene
I'm not quite understanding what's needed,
I've had a go with the attached, as I mentioned I couldn't see your reference to purple and green cells.
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). Does that help?