Keeping track of the Hours of Service data for local drivers can be a hassle.
You have worked for 28 hours this working period (max 60 hrs) Your average working time is: 9.5 hours per week over a rolling 17 weeks (max 48 hrs per week) 9.0 hours per week over a fixed 18 weeks from 2nd Dec 2013 to 7th Apr 2014 (max 48 hrs per week) 6.2 hours per week over a rolling 26 weeks (max 48 hrs per week) Your 3 week overview. 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 and alert you to impending fatigue violations. If, for example, the hours worked for each day are in cells E2 to E6, if you enter SUM (E2:E6) in E7, it will give you the total for the week. Entering 'E7-37' in E8 will give you the excess (or shortfall) for the week.
When your drivers meet the requirements to use timesheets rather than log books, you are still responsible to maintain appropriate records of time. Filling out paper forms is time consuming and prone to mistakes.
That's why we developed the HoS Timesheet template for Excel. It works with Excel 2007 and higher, allowing you to enter the timesheet information electronically. It is easier to enter, correct errors and to store. It also meets the requirements for timesheets under the various provincial regulations.
Note: It does not meet the requirements for a log book, so drivers that do not qualify for the 160 km rule must still keep log books. This can be a useful tool for monitoring the compliance of the drivers with the HoS rules.
If you are interested purchasing in this template, contact us for more details.
Video Tutorials
We've created a series of video tutorials that will show you how the template is used.
Installation and Customization
The template comes in a zip file with some extra documentation and license file. All you need to do is extract the template file and put it where you need it. Then you can customize the settings as needed.
Creating a Driver Workbook
You will need to create a workbook for each driver.
Adding Months
The workbooks are organized into months. Every month you'll need to add the current month (you can do this ahead of time if you like).
Entering Time Values
Entering time values is easy. Just start with a default and modify it accordingly.
Note: The latest version will automatically set the status of multiple cells that are selected within a duty status. There is no longer any need to right click and select Set Status as shown in the video.
Also, the problem with dragging cells has been dealt with and you should not see that in the newer versions.
Removing Months
Since you are only required to keep 6 months of data, you can delete older months from the workbook.
Just make sure you don't delete a month too early. You can't undo this.
Adding basic labels
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.
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:
- Time spent working before lunch
- Time spent at lunch
- Time spent working after lunch
Video Tutorials
We've created a series of video tutorials that will show you how the template is used.
Installation and Customization
The template comes in a zip file with some extra documentation and license file. All you need to do is extract the template file and put it where you need it. Then you can customize the settings as needed.
Creating a Driver Workbook
You will need to create a workbook for each driver.
Adding Months
The workbooks are organized into months. Every month you'll need to add the current month (you can do this ahead of time if you like).
Entering Time Values
Entering time values is easy. Just start with a default and modify it accordingly.
Note: The latest version will automatically set the status of multiple cells that are selected within a duty status. There is no longer any need to right click and select Set Status as shown in the video.
Also, the problem with dragging cells has been dealt with and you should not see that in the newer versions.
Removing Months
Since you are only required to keep 6 months of data, you can delete older months from the workbook.
Just make sure you don't delete a month too early. You can't undo this.
Adding basic labels
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.
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:
- 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.
Formula 1: Calculating daily working hours
Work Hours Calculator Excel Template
Here's how you calculate time in a daily Excel timesheet template:
Work Hours Calculator
- Insert =sum(D8+F8) into the 'Daily total' cell (marked as G8 in this timesheet example).
- As soon as employees type the hours in the 'Morning hours' and 'Afternoon hours' cells, this time automatically gets added to the 'Daily total'.
- To use the same '=sum()' formula on the remaining days, expand the selection in your table to cover the remaining cells.
- 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)').
- 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'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'.
- Insert =sum(G14+G30+N14+N30) into the 'Monthly total' cell (marked as P4 in this timesheet example).
- 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.
- Select all the cells you want to unlock by holding the Ctrl key and clicking or expanding on the needed cells
- Press the Ctrl+1 on your keyboard to get the Format cells window.
- Select the Protected tab within the Format cells window, untick the Locked checkbox, and click OK to close the dialogue box.
- Once you've differentiated between locked and unlocked cells, you'll need to make sure they stay that way, by 'protecting' the sheet
- In order to open the 'protect sheet' window, press and hold Alt+H+O+P
- Once you've opened this window, make sure only the option 'select unlocked cells' is ticked.
- 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.
- 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.
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:
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:
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:
