Most small business owners wear multiple hats and run around all day like a headless chicken trying to complete many different kinds of tasks. Your employees probably do the same thing.
It’s not unusual in a small business for your employees to go from working the register to stocking the freezer to driving goods to a customer’s house for delivery in the span of a few hours. If you work in construction, it can get even more complicated, with employees working at three different jobs on the same day.
In this article, we’ll go over how to do payroll and calculate overtime for employees who turn in hours for jobs at different rates throughout the week.
Overview: What is blended overtime (OT)?
You are required to pay overtime rates, time and a half or 1.5 times the normal pay rate, for any hours worked over 40, which is an average workweek. For an employee who is paid at the same rate for all tasks, that’s easy to calculate. For other workers, the process is a little more complicated.
For example, construction workers often have different responsibilities at different jobsites. They may have pay for each job determined by government or union requirements.
In order to calculate time and a half for these types of workers, you use the blended rate formula. It enables you to calculate overtime for employees who have worked at different jobs or worked shifts with different responsibilities, earning different pay rates for each, during the pay period.
How to calculate blended overtime rates
Josef Black is one of Use The Schwartz Construction’s most versatile employees. Most of his average work hours per year are spent as a laborer. But he can also do carpentry and drywall work when it is needed for various jobs. His pay rate as a laborer is $25 per hour. It gets boosted to $30 for carpentry, and $40 for drywall.
Here is a normal week’s time card:
Let’s go over the steps to calculate Josef’s total pay for the week.
Step 1: Calculate gross wages
First, calculate gross wages from base pay with no overtime charge. For Josef, that is the laborer rate of:
$25/hr x 51 hours = $1,275
Step 2: Calculate extra pay
The actual accounting term for extra pay is shift differential. Josef’s shift differential is $5 for carpentry and $15 for drywall. For this week, the calculation is:
($5 x 16) + ($15 x 16) = $320
Step 3: Add together for total pay and calculate the rate
The sum of steps one and two is the total pay:
$320 + $1,275 = $1,595
This total of $1,595 over 51 hours reflects a blended pay rate of $31.27. Normal pay over the 40-hour workweek is the blended rate times 40, which is $1,250.98.
Step 4: Calculate blended overtime and find final pay
Josef worked 51 hours this week, which means there will be 11 hours of weighted overtime pay. The blended overtime rate is equal to the blended rate multiplied by 1.5:
$31.27 x 1.5 = $46.91
Find overtime pay by multiplying that rate by the 11 hours of overtime worked:
$46.91 x 11 = $516.03
Finally, add the overtime pay to the normal pay to find what Josef should receive on his paycheck:
$1,250.98 + $516.03 = $1,767.01
How to calculate blended overtime in a time card spreadsheet
The above calculations should all happen in your payroll software automatically. I would recommend also having a backup in your time card spreadsheet. It isn’t strictly necessary to calculate blended OT rates twice during the payroll process, but it is good to have the check figure.
In my experience, employees like nothing less than surprises on their paychecks. Giving them a way to see how their final pay is calculated is better than having them looking at an online overtime calculator that could be giving them the wrong answer.
The following graphic will show the time card from above as well as the formula for each step of the process. I’ll go over some Excel tips afterward.
Here are a few best practices for the spreadsheet.
- Control the sheet: In Excel, you can use the Review tab on the top ribbon to lock down the whole sheet. I would do this and allow edits only to the hour cells. Create a custom time card for each employee with their normal rate and which classifications they can use, and then allow them to fill in the hours.
- Check with the software: If you have only a few employees, it’s worth taking the time to check all time cards against the payroll report from your software. If you have a lot of employees, sample several time cards and compare them to your software report for control purposes.
- Have a supervisor sign off: It’s critical that someone in a leadership position signs off on each time card. Lying on time cards is one of the easiest ways for employees to steal from your business.
- Make it simple: You don’t need to show every single step of the calculation as this sheet does. Combine or hide rows to make the sheet easier to read. In this example, you would be OK showing just the base rate, blended rate, normal pay, OT pay, and final pay rows.
- Don’t hard-code: Hard-coding is when you use numbers in a formula instead of linking to a cell. In the above example, the only number I hard-coded is 40, which is the amount of hours in a normal workweek. I hard-coded this number because it will never change. Every other number, such as pay rate, or OT hours, can be changed based on the week or if the employee gets a raise. Assigning those numbers to a cell will allow you to make changes without having to change each individual formula.
- Learn more about Excel: You don’t have to be at college-professor level, but knowing the basics of Excel will help you to create spreadsheets like this and, when necessary, fix or adjust them.
Add a little whey protein to your overtime smoothie
Running a business is complicated, so anything you can do to make payroll less complicated is worth it. Learning how to calculate blended overtime brings you one step closer to fully understanding how payroll works.
Now you can be confident that you're paying the correct rates, and your employees can follow along and be confident that their paychecks won’t be short.