Excel Function Friday: Track Driver Hours

Thanks for your formula suggestions on Wednesday’s blog post about promotional pricing.

Here’s another formula example, and I’m sure you’ll have alternate methods for this problem too.

Driver Limits

In some countries, there are limits to the hours that truck drivers can work in a string of consecutive days.

In this example, the limit is 60 hours, in any period of 7 consecutive days.

Worksheet Entry Cells

The maximum hours is entered in cell C1, and the number of consecutive days is entered in cell F1.

If the regulations change, it will be easy to change those settings.

Worksheet Entry Cells
Worksheet Entry Cells

Calculate Remaining Hours

To help prevent drivers from going over their limits, we’ll set up a table where the daily hours are entered.

The date and driver name are entered in each row, in columns B and C.

In column D, the following  SUMPRODUCT formula calculates how many hours the driver has remaining, in the current 7 day period.

=$C$1-(SUMPRODUCT(--($B$4:$B4>=$B4-F$1-1), --($C$4:$C4=$C4), --($E$4:$E4))-E4)

The SUMPRODUCT formula checks all the rows above the formula’s row, where:

  • the date within the 7 day range
  • the driver name matches the name in the current row.

That amount is subtracted from the maximum hours allowed.

DriverHours01

Calculate the Consecutive Hours

The current hours are typed in column E, and a simple formula in column F calculates the total for a consecutive 7 day period.

=$C$1-D4+E4

DriverHours02

Highlight the Violations

With Conditional Formatting, you can highlight any cells where the total consecutive hours exceeds the maximum allowed.

  • On the Ribbon’s Home tab, click Conditional Formatting
  • Click Highlight Cells Rules, and then click Greater Than

DriverHours03

  • In the Greater Than dialog box, select cell C1 as the limit in the text box.
  • Select one of the preset format, or create a custom format to highlight the cells.

DriverHours04

View the Results

With the conditional formatting applied, it’s easy to see where the trouble is.

In this example, Lou has gone over the limit on April 10th.

DriverHours05

Download the Driver Limit Sample File

To see the data and the formulas, you can download the Driver Hours Limit sample file. The file is zipped, and is in Excel 2003 format.

There is also a pivot table that totals the drivers’ hours per calendar week.
__________

10 thoughts on “Excel Function Friday: Track Driver Hours”

  1. assuming the list can get pretty long, pretty fast – would it be useful to switch to index():B4 for the search ranges and match the start on first occurrence of DATE = TODAY-7

    Or, is sumproduct() efficient enough to handle multiple columns in the 10000 data point range?

  2. Debra – will you please break down the sumproduct formula? I’m having a hard time understanding exactly what it does.

  3. Bob Phillips (MS Excel MVP like Debra) has a detailed white paper on SUMPRODUCT available on his site (this also covers Boolean coercion techniques)

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Most would agree that SUMPRODUCT is far from efficient (akin to Arrays – iterative by nature) and should be used in moderation
    where viable consider use of SUMIFS / COUNTIFS in preference to SUMPRODUCT

  4. Hi,

    I could not understand the sumproduct part of the formula, especially with the double negative signs in it.

    Could you please explain that bit in more detail?

    Thanks,
    Abbas

  5. Abbas, see the link provided in the prior post which covers the SUMPRODUCT in detail and also coercion methods (double unary for ex.)

  6. Ok,

    But this table does not cover the important calcualtion of driven hours plus on-duty, non-driving and how that affected the total hours rules in conjunction with the max hours in the 60 & 70 hour rules.

  7. @ Debrah,
    Thanks for your smart example. I copied it in my DiversenBerekeningen.xlsm (sorry for the Dutch but that’s me).
    My work: formatting and some explanation for Tam and Ans.
    I tried – but idn’t succeed – to redo your work with SUMIFS and the like.
    Frans

  8. I can’t get my function to work. I use your exact formula with the same placement of data in cells. Instead, I used 70 hours and 8 days though. But the formula will not copy down to the following rows so it can continue to calculate. It reports back a value of ” #VALUE! ” instead of actual numbers. What am I doing wrong?

  9. Our drivers can not work more than eight 12-hour days in a 30 day period. Would you be able to help me create a spreadsheet to keep track of this? (i wasn’t able to open your samples)

Leave a Reply to Peter Psilakis Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.