Mashhura Jahan
If you want to calculate payroll for overtime then this article will be beneficial for you. The focus of this article is to explain how to calculate payroll overtime with formula in Excel. Different examples will be explained for your convenience.
Download Practice Workbook
You can download the practice workbook from here.
Formula for Overtime Payroll
The formula for Regular Payment is:
And, the formula for Overtime Payment is:
So, the formula for Daily Payment becomes:
4 Ideal Examples to Calculate Payroll Overtime with Formula in Excel
To explain this article, I have taken the following worksheet. It contains Employee ID, Entry Time, Exit Time, and Hourly Payment. I will use this dataset to calculate overtime payroll with formula in Excel. Here, I will explain 4 different examples to calculate in 4 different ways.
1. Use IF Function to Calculate Overtime Payroll with Formula in Excel
In this first method, I will use the IF function to calculate Overtime and then calculate overtime payroll from it. Let’s see the steps.
Steps:
- Firstly, select the cell where you want the Working Hours. Here, I selected Cell F8.
- Secondly, write the following formula in Cell F8.
=(D8-C8)*24
- Thirdly, press Enter to get the result.
- After that, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to the other cells and got the Working Hours for each employee.
- Next, select the cell where you want to calculate the Regular Time.
- Then, write the following formula in that selected cell.
=IF(F8>=$E$4,$E$4,F8)
- After that, press Enter to get the result.
- Next, drag the Fill Handle down to copy the formula to the other cells.
- Then, select the cell where you want the Overtime. Here, I selected Cell H8.
- Next, in Cell H8 write the following formula.
=F8-G8
- After that, press Enter to get the result.
- Further, drag the Fill Handle down to copy the formula to the other cells.
- Now, select the cell where you want to calculate the Daily Payment.
- Next, write the following formula in that selected cell.
=(E8*G8)+(E8*H8*1.5)
- Afterward, press Enter to get the Daily Payment.
🔎 How Does the Formula Work?
- (E8*G8): Here, the formula multiplies the Hourly Payment by the Regular Time.
- (E8*H8*1.5): Now, the formula multiplies the Hourly Payment by the Overtime. And then multiply it by 1.5.
- (E8*G8)+(E8*H8*1.5): Here, the formula returns the summation of these two values.
- Finally, drag the Fill Handle down to copy the formula to the other cells.
Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
2. Employ MIN Function to Determine Overtime Payroll in Excel
Here, I will use the MIN function to determine overtime payroll with a formula in Excel. Let’s see how you can do that.
Steps:
- To begin with, calculate Working Hours by following the steps from Method-01.
- Then, select the cell where you want to calculate the Regular Time. Here, I selected Cell G8.
- Next, in Cell G8 write the following formula.
=MIN($E$4,F8)
- After that, press Enter to get the result.
- After that, drag the Fill Handle down to copy the formula to the other cells.
- Next, select the cell where you want the Overtime.
- Then, write the following formula in that selected cell.
=F8-G8
- After that, press Enter to get the result.
- Then, drag the Fill Handle down to copy the formula to the other cells.
- Further, select the cell where you want the Daily Payment. Here, I selected Cell I8.
- Then, in Cell I8 write the following formula.
=(E8*G8)+(E8*H8*1.5)
- Finally, press Enter to get the Daily Payment.
🔎 How Does the Formula Work?
- (E8*G8): This part of the formula multiplies the Hourly Payment by the Regular Time.
- (E8*H8*1.5): Now, the formula multiplies the Hourly Payment by the Overtime. And then multiply it by 1.5.
- (E8*G8)+(E8*H8*1.5): Here, the formula returns the summation of these two values.
- Lastly, drag the Fill Handle down to copy the formula to the other cells.
Read More: How to Create Payroll Calculator in Excel (with Easy Steps)
3. Apply MAX Function to Find Overtime Payroll with Formula in Excel
You can also use the MAX function to calculate the Overtime. And then calculate the overtime payroll from it. Let’s see the steps.
Steps:
- In the beginning, calculate the Working Hours for every employee by following the steps from Metod-01.
- Afterward, select the cell where you want to calculate the Overtime.
- Then, write the following formula in that selected cell.
=MAX(0,F8-$E$4)
- Next, press Enter to get the Overtime.
🔎 How Does the Formula Work?
- F8-$E$4: Here, the formula subtracts the value in Cell E4 from the value in Cell F8.
- MAX(0,F8-$E$4): Now, in the MAX function, I selected 0 as number1 and F8-$E$4 as number2. The formula returns the largest number among these two numbers.
- Further, drag the Fill Handle down to copy the formula to the other cells.
- Next, select the cell where you want the Regular Time. Here, I selected Cell H8.
- Subsequently, in Cell H8 write the following formula.
=F8-G8
- Afterward, press Enter to get the result.
- Finally, drag the Fill Handle down to copy the formula to the other cells.
- Next, select the cell where you want the Daily Payment.
- Then, write the following formula in the selected cell.
=(E8*H8)+(E8*G8*1.5)
- Afterward, press Enter and you will get the Daily Payment.
🔎 How Does the Formula Work?
- (E8*H8): Here, the formula multiplies the Hourly Payment by the Regular Time.
- (E8*G8*1.5): Now, the formula multiplies the Hourly Payment by the Overtime. And then multiply it by 1.5.
- (E8*H8)+(E8*G8*1.5): This formula returns the summation of these two numbers.
- In the end, drag the Fill Handle to copy the formula to the other cells.
Read More: How to Make a Payroll System in Microsoft Excel with Payslip
4. Calculate Overtime Payroll in Excel Considering Lunch Break
In this example, I will consider the lunch break while calculating the Overtime. I have taken the following dataset. It contains two more columns for Lunch Starts and Lunch Ends. I will use this dataset to calculate the payroll for overtime with a formula in Excel.
Let me show you how you can do it.
Steps:
- Firstly, select the cell where you want to calculate the Regular Time.
- Secondly, write the following formula in that selected cell.
=IF((((D8-C8)+(F8-E8))*24)>$G$4,$G$4,(((D8-C8)+(F8-E8))*24))- Thirdly, press Enter to get the result.
🔎 How Does the Formula Work?
- (D8-C8): This part of the formula subtracts the Entry Time from the starting time for lunch.
- (F8-E8): Now, the formula subtracts the ending time for lunch from the Exit Time.
- ((D8-C8)+(F8-E8)): Here, the formula returns the summation of these two values.
- (((D8-C8)+(F8-E8))*24): Now, 24 is multiplied by the summation.
- IF((((D8-C8)+(F8-E8))*24)>$G$4,$G$4,(((D8-C8)+(F8-E8))*24)): Finally, the IF function checks for if (((D8-C8)+(F8-E8))*24) is greater than the value in Cell G4. If the logical_test is True then the formula returns G4. Otherwise it returns (((D8-C8)+(F8-E8))*24).
- After that, drag the Fill Handle down to copy the formula to the other cells.
- Next, select the cell where you want the Overtime.
- Then, write the following formula in that selected cell.
=(((D8-C8)+(F8-E8))*24)-H8- Subsequently, press Enter to get the result.
🔎 How Does the Formula Work?
- (D8-C8): Here, the formula subtracts the Entry Time from the starting time for lunch.
- (F8-E8): This part of the formula subtracts the ending time for lunch from the Exit Time.
- ((D8-C8)+(F8-E8)): Here, the formula returns the summation of these two values.
- (((D8-C8)+(F8-E8))*24): Now, 24 is multiplied by the summation.
- (((D8-C8)+(F8-E8))*24)-H8: Finally, the value in Cell H8 is subtracted from the result.
- Afterward, drag the Fill Handle down to copy the formula to the other cells.
- Then, select the cell where you want the Daily Payment.
- Next, write the following formula in that selected cell.
=(G8*H8)+(G8*I8*1.5)
🔎 How Does the Formula Work?
- (G8*H8): Here, the formula multiplies the Hourly Payment by the Regular Time.
- (G8*I8*1.5): Now, the formula multiplies the Hourly Payment by the Overtime. And then multiply it by 1.5.
- (G8*H8)+(G8*I8*1.5): Finally, the formula returns the summation of these two values.
- Finally, drag the Fill Handle down to copy the formula to the other cells.
Read More: How to Generate Payroll in Excel VBA (with Easy Steps)
How to Calculate Conditional Overtime in Excel
In this section, I will show you how to calculate conditional overtime in Excel. In some cases, all the overtime is not calculated as overtime. It needs to be more than a certain hour to be counted as overtime. Let’s see the steps of this calculation.
Steps:
- In the beginning, calculate the Working Hours by following the steps from Method-01.
- After that, select the cell where you want to calculate the Overtime. Here, I selected Cell G8.
- Then, in Cell G8 write the following formula.
=IF(F8>$E$4,F8-$E$4,0)
- Next, press Enter to get the result.
- Afterward, drag the Fill Handle down to copy the formula to the other cells.
- Then, select the cell where you want to determine the Conditional Overtime.
- Further, write the following formula in that selected cell.
=IF(G8>1,G8,0)
- Next, press Enter to get the result.
- In the end, drag the Fill Handle down to copy the formula to the other cells.
Practice Section
Here, I have provided a practice sheet for you to practice how to calculate payroll for overtime with formula in Excel.
Conclusion
So, you have reached the end of my article. Here, I tried to explain how to calculate payroll for overtime with formula in Excel with 4 different examples. I hope this article was helpful to you. If you have any questions, feel free to let me know in the comment section below.
