Drivers Hours Calculator Excel

1. Free tools to calculate hours worked: Add up hours and minutes with our easy Time Calculator Hourly Paycheck Calculator will calculate the hours worked and the gross wages due Timesheet calculator - will calculate bi-weekly Timesheets Time Card Calculator Geek - will give you the most options to customize lunch, overtime, military time and much.
2. Formula 1: Calculating daily working hours. Here's how you calculate time in a daily Excel timesheet template: 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'.
3. Click the cell D11, copy and paste formula = (C11-B11). 24 into the Formula Bar, and press Enter key.

1. Drivers Hours Calculator Excel Formula
2. Hours Calculator With Lunch
3. Drivers Hours Calculator Excel Template

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. National Safety Code — Hours of Service. Hours of Service - Industry Update (PDF 93 KB) Motor Vehicle Act Regulations - Division 37 - Safety Code. Hours of Service Worksheets: Cycle 1 HOS Worksheet (Excel 105 KB) Logger HOS Worksheet (Excel 103 KB) New Hours of Service Regulations Announcement (PDF 37 KB) Summary of Hours of Service Rules.

It is easy to build an Excel formula to calculate hours worked and overtime of your employees. This is why Excel is widely used in the workplaces. It makes any calculation very easy.

The Excel template I have made for this article can be directly used in your workplace.

Most companies calculate work hours and over time based on the 40-hours-work-week (8 hours per day). By default, my Excel template also calculate work hours and overtime based on the 40-hours-work-week. But if your company takes different hours-work-week, you can define that in the template.

In this Excel template, over time is calculated based on the whole week, not based on an individual day. Suppose, an employee has worked 9 hours on the very first day of the week. But his total hours in the week does not exceed 40 hours, he will not be rewarded any over time.

You can also define different hourly rates for Regular Works and Over Time Works.

Table of Contents

• Excel formula to calculate hours worked and overtime

Calculate Hours Worked and Overtime Excel Template

Download the Excel template that I have built for this article.

Excel formula to calculate hours worked and overtime

This is the template overview. This template is printable in one page (perfect for Letter and A4 Page with Landscape Orientation) without any modification.

The inputs in the Excel Template

Put the following inputs in the Excel template (image below):

Overtime Counted After: Generally, this is 40 hours per week. If your office maintains 45 or other working hours, input that value in this field. Working hours vary from country to country.

Hourly Rate:

Normally Working Hour Rate is different from the Over Time Hourly Rate.

• Regular: Input the Regular Hourly Rate
• Over Time: Input the Over Time Hourly Rate

The template takes 4 time-inputs (above image):

• Time In: This is the time when the employee enters into the working place.
• Lunch Starts: Time when the lunch starts in the working place.
• Lunch Ends: Lunch end time entry.
• Time Out: This is when the employee completely leaves the office place.

Outputs we get automatically

These are the outputs we get from our inputs:

1) Total Hours:

This 'Total Hours' is calculated for an individual day. We have used this formula in the cell G16 to get the total hours: =((F16-C16)-(E16-D16))*24

• (F16-C16) is actually (Time Out – Time In). And (E16-D16) is (Lunch Ends – Lunch Starts).
• We multiplied ((Time Out – Time In) – (Lunch Ends – Lunch Starts)) by 24 to covert it into hour value.
• This is why we get the value in Number Otherwise, subtraction of two times results in Time format.

2) Over Time Hours

Though the template shows the Regular Hours before the Over Time Hours, I actually calculated the Over Time Hours before the Regular Hours. This is the formula I have used in the cell I16 to calculate the Over Time Hours:

=IF(SUM($G$16:G16)>work_hours_per_week,SUM($G$16:G16)-work_hours_per_week,0)

Where, work_hours_per_week = D12

How does this formula work?

• This formula has an expanding range. It is $G$16:G16. For the next cell (I17) in the column, this range will be: $G$16:G17. You see the range has expanded. This is why it is called expanding range.
• logical_test: IF function checks whether the sum of the expanding range has exceeded the value of work_hours_per_week.
• value_if_true: If the sum exceeds the value, the IF function returns this value: SUM($G$16:G16)-work_hours_per_week.
• value_if_false: Otherwise the IF function returns value 0.

Let me check this formula for the cell I21: =IF(SUM($G$16:G21)>work_hours_per_week,SUM($G$16:G21)-work_hours_per_week,0)

• =IF(47.25>40,47.25-40,0); [I have replaced some parts of the formula with their values]
• =IF(TRUE,7.25,0)
• =7.25

So, the formula returns value 7.25.

3) Regular Hours

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. National Safety Code — Hours of Service. Hours of Service - Industry Update (PDF 93 KB) Motor Vehicle Act Regulations - Division 37 - Safety Code. Hours of Service Worksheets: Cycle 1 HOS Worksheet (Excel 105 KB) Logger HOS Worksheet (Excel 103 KB) New Hours of Service Regulations Announcement (PDF 37 KB) Summary of Hours of Service Rules.

It is easy to build an Excel formula to calculate hours worked and overtime of your employees. This is why Excel is widely used in the workplaces. It makes any calculation very easy.

The Excel template I have made for this article can be directly used in your workplace.

Most companies calculate work hours and over time based on the 40-hours-work-week (8 hours per day). By default, my Excel template also calculate work hours and overtime based on the 40-hours-work-week. But if your company takes different hours-work-week, you can define that in the template.

In this Excel template, over time is calculated based on the whole week, not based on an individual day. Suppose, an employee has worked 9 hours on the very first day of the week. But his total hours in the week does not exceed 40 hours, he will not be rewarded any over time.

You can also define different hourly rates for Regular Works and Over Time Works.

Table of Contents

• Excel formula to calculate hours worked and overtime

Calculate Hours Worked and Overtime Excel Template

Download the Excel template that I have built for this article.

Excel formula to calculate hours worked and overtime

This is the template overview. This template is printable in one page (perfect for Letter and A4 Page with Landscape Orientation) without any modification.

The inputs in the Excel Template

Put the following inputs in the Excel template (image below):

Overtime Counted After: Generally, this is 40 hours per week. If your office maintains 45 or other working hours, input that value in this field. Working hours vary from country to country.

Hourly Rate:

Normally Working Hour Rate is different from the Over Time Hourly Rate.

• Regular: Input the Regular Hourly Rate
• Over Time: Input the Over Time Hourly Rate

The template takes 4 time-inputs (above image):

• Time In: This is the time when the employee enters into the working place.
• Lunch Starts: Time when the lunch starts in the working place.
• Lunch Ends: Lunch end time entry.
• Time Out: This is when the employee completely leaves the office place.

Outputs we get automatically

These are the outputs we get from our inputs:

1) Total Hours:

This 'Total Hours' is calculated for an individual day. We have used this formula in the cell G16 to get the total hours: =((F16-C16)-(E16-D16))*24

• (F16-C16) is actually (Time Out – Time In). And (E16-D16) is (Lunch Ends – Lunch Starts).
• We multiplied ((Time Out – Time In) – (Lunch Ends – Lunch Starts)) by 24 to covert it into hour value.
• This is why we get the value in Number Otherwise, subtraction of two times results in Time format.

2) Over Time Hours

Though the template shows the Regular Hours before the Over Time Hours, I actually calculated the Over Time Hours before the Regular Hours. This is the formula I have used in the cell I16 to calculate the Over Time Hours:

=IF(SUM($G$16:G16)>work_hours_per_week,SUM($G$16:G16)-work_hours_per_week,0)

Where, work_hours_per_week = D12

How does this formula work?

• This formula has an expanding range. It is $G$16:G16. For the next cell (I17) in the column, this range will be: $G$16:G17. You see the range has expanded. This is why it is called expanding range.
• logical_test: IF function checks whether the sum of the expanding range has exceeded the value of work_hours_per_week.
• value_if_true: If the sum exceeds the value, the IF function returns this value: SUM($G$16:G16)-work_hours_per_week.
• value_if_false: Otherwise the IF function returns value 0.

Let me check this formula for the cell I21: =IF(SUM($G$16:G21)>work_hours_per_week,SUM($G$16:G21)-work_hours_per_week,0)

• =IF(47.25>40,47.25-40,0); [I have replaced some parts of the formula with their values]
• =IF(TRUE,7.25,0)
• =7.25

So, the formula returns value 7.25.

3) Regular Hours

To calculate Regular Hours, I have used this formula in the cell H16: =MAX(G16-I16,0)

This formula returns the maximum one between these two values: G16-I16 and 0.

4) Total Hours (Regular)

5) Total Hours (Over Time)

Cell I22 actually holds our total Over Time hours. So, we just refer that cell in the cell I23.

6) Total Pay (Regular)

Multiplication of the Regular Hourly Rate and total Regular Hours.

7) Total Pay (Over Time)

Multiplication of the Over Time Rate and total Over Time Hours.

8) Grand Total Pay

Drivers Hours Calculator Excel Formula

Sum of Regular Pay and Over Time Pay.

Conclusion

Whatever hourly rate your company provides and whatever working hours per week your company has adopted, this template will suit your company. Even if you did understand how the formulas work, you can modify and extend this template for fulfilling your specific criteria. So, these are the Excel formulas I use to calculate hours worked and overtime in a week.

Hours Calculator With Lunch

Related Readings

Drivers Hours Calculator Excel Template