How to Calculate Blended Overtime Pay

Blended overtime is used to calculate pay for employees who work shifts at different pay rates during the same pay period. The Ascent guides you through calculating blended overtime.

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:

Josef worked Monday through Saturday and had hours in all three classifications. Image source: Author

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.

Don’t be afraid to hide or combine some of the interim calculation rows for simplicity. Image source: Author

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.