Forum Discussion

Copper Contributor

Nov 06, 2018

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 H...

Formulas and Functions

Wyn Hopkins

MVP

Nov 08, 2018

Hi Sharlene

How about the attached

HOS - DRIVER HOURS (potential solution).xlsx13 KB

Sharlene Yadlowsky

Copper Contributor

Nov 08, 2018

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.

HOS - DRIVER HOURS (online forum).xlsx16 KB

Wyn Hopkins
MVP
Nov 10, 2018
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.

HOS - DRIVER HOURS (online forum) (2).xlsx16 KB
- Sharlene Yadlowsky
  Copper Contributor
  Nov 13, 2018
  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?