Drivers Hours Calculator Excel

Adding basic labels

  1. Time Calculator
  2. Drivers Hours Calculator Excel Template
  3. Work Hours Calculator

Truck drivers: save yourself a fine and maximise your driving hours with this advanced tachograph and driving log. TruckerTimer is the only application designed specifically for truck, lorry and HGV drivers in the UK and Europe that will help you track your driving hours. Click the cell D11, copy and paste formula = (C11-B11). 24 into the Formula Bar, and press Enter key. I am working on a monthly Drivers hours spreadsheet but cannot get the excel formula for drivers daily rest finishing one day at 17:30 and starting next day at 05:30 - does anyone know how to get this to calculate the 12:00 - it is not taking away but adding the hours to midnight plus the additional 05.30.

First, you'll need to decide what kind of basic data you need in your Excel time sheet. The most common approach is to add the following basic labels:

  • Employee name
  • Date range covered in the timesheet (day, week, or month)
  • Name of the company department
  • Manager name

You can add more information if you want or need to, but a simple approach is always best.

Work hours calculator

Adding timesheet labels

Now that you've added the basic labels, you'll need to add timesheet labels to your template.

Add the dates, and divide the columns in order to clearly organize the most important data:

Excel
  • Time spent working before lunch
  • Time spent at lunch
  • Time spent working after lunch

For clarity, we'll mark these times in the timesheet as 'Morning hours' 'Lunch time', and 'Afternoon hours'.

In the next step, we'll see how to add up 'Morning hours' and 'Afternoon hours' to get the 'Daily total' automatically.

Drivers Hours Calculator Excel

Formula 1: Calculating daily working hours

Here's how you calculate time in a daily Excel timesheet template:

  1. Insert =sum(D8+F8) into the 'Daily total' cell (marked as G8 in this timesheet example).
  2. As soon as employees type the hours in the 'Morning hours' and 'Afternoon hours' cells, this time automatically gets added to the 'Daily total'.
  3. To use the same '=sum()' formula on the remaining days, expand the selection in your table to cover the remaining cells.
  4. Expanding the selection to calculate the remaining days will be important when you want to calculate 'Weekly totals'.

    Formula 2: Calculating weekly working hours

    Here's how you calculate time in a weekly Excel timesheet template:

    1. Use the =sum() formula again in the 'Weekly total' cell with the data in the 'Daily total' column, by selecting the G8 cell, and then expanding the selection to G14 ('=sum(G8:G12)').
    2. As soon as employees add hours in the 'Morning hours' and 'Afternoon hours' cells, this time automatically get added to their 'Daily total', and this data automatically gets added to their 'Weekly total'.

    You can create multiple tables like this in one Excel sheet, and then calculate 'Daily totals' and 'Weekly totals' for each. Once you've had 'Weekly totals' calculated, you'll use this data to calculate the 'Monthly total'.

    Formula 3: Calculating monthly working hours

    Here's how you calculate time in a monthly Excel timesheet template:

    1. You'll need to select the 'Weekly total' cells (marked as G14, G30, N14, N30 in this Excel timesheet example) in order to calculate the 'Monthly total'.
    2. Insert =sum(G14+G30+N14+N30) into the 'Monthly total' cell (marked as P4 in this timesheet example).
    3. Again, as employees enter their 'Morning hours' and 'Afternoon hours' in the right cells, the 'Daily totals' and 'Weekly totals' get calculated, and the 'Monthly total' gets calculated as well.

    If employees change data in any of the cells, these changes will be reflected in the Monthly total.

    Protecting cells and the timesheet

    Once you've created your templates and defined all formulas correctly, you'll want to lock certain cells, in order to make sure your formulas remain functional and correct.

    You'll also want to make sure certain cells remain unlocked, so that your employees can freely add their data - typically, you'll need 'Morning hours', 'Lunch time', 'Afternoon hours', and 'Name' cells unlocked, and formula cells locked.

    By default, all your Excel cells are locked, but you'll be able to manage them in the Format cells window.

    In order to unlock cells for employee editing, simply:

    1. Select all the cells you want to unlock by holding the Ctrl key and clicking or expanding on the needed cells
    2. Press the Ctrl+1 on your keyboard to get the Format cells window.
    3. Select the Protected tab within the Format cells window, untick the Locked checkbox, and click OK to close the dialogue box.
    4. Once you've differentiated between locked and unlocked cells, you'll need to make sure they stay that way, by 'protecting' the sheet
    5. In order to open the 'protect sheet' window, press and hold Alt+H+O+P
    6. Once you've opened this window, make sure only the option 'select unlocked cells' is ticked.
    7. Add and remember a password, then close the dialogue box - your employees will have to enter this password in order to edit the unlocked cells.
    8. The employees will need to press and hold Alt+H+O+P to open the dialogue box again, enter the password and enable cell editing - considering they only have the password to edit unlocked sheets, your locked sheets will remain uneditable, with formulas intact.

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.

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.

Calculate the 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 column D, a SUMPRODUCT formula calculates how many hours the driver has remaining, in the current 7 day period.

The SUMPRODUCT formula checks all the rows above, where the date within the 7 day range, and the driver name matches the current row.
That amount is subtracted from the maximum hours allowed.

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

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

Drivers

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

Time Calculator

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

View the Results

With the conditional formatting applied, it's easy to see where the trouble is. In this example, Lou has gone over his limit on April 10th.

Download the Driver Limit Sample File

Drivers Hours Calculator Excel Template

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.

Hours calculator with lunch

Work Hours Calculator

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