How to Calculate Payroll Overtime with Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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:

Regular Payment = Regular Time * Hourly Payment

And, the formula for Overtime Payment is:

Daily Overtime Payment = Overtime Hours * Hourly payment * 1.5

So, the formula for Daily Payment becomes:

Daily Payment = (Regular Time * Hourly Payment) + (Overtime Hours * Hourly payment * 1.5)

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.

Dataset to Calculate Payroll Overtime with Formula in Excel


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

Use IF Function to Calculate Overtime Payroll with Formula in Excel

  • Thirdly, press Enter to get the result.

Performing Mathemetical Operations to Calculate Working Hour in Overtime Payroll Formula in Excel

Here, the Entry Time will be subtracted from the Exit Time and then the result will be multiplied by 24.
  • After that, drag the Fill Handle down to copy the formula.

Dragging Fill Handle to Copy Payroll Overtime Formula in Excel

  • 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)

Using IF Function to Calculate Regula Time for Payroll Overtime formula in Excel

  • After that, press Enter to get the result.

Now, in the IF function, I selected F8>=$E$4 as logical_test, $E$4 as value_if_true, and F8 as value_if_false. The formula will check if the value in Cell F8 is greater than or equal to the value in Cell E4. If the logical_test is true then the formula will return the value in Cell E4. Otherwise, it will return the value in Cell F8.
  • Next, drag the Fill Handle down to copy the formula to the other cells.

Dragging Fill Handle to Copy Regular Time Formula to Calculate Overtime Payroll in Excel

  • Then, select the cell where you want the Overtime. Here, I selected Cell H8.
  • Next, in Cell H8 write the following formula.
=F8-G8

Calculating Overtime to Use it in Overtime Payroll Formula in Excel

  • After that, press Enter to get the result.

Here, the formula subtracts the Regular Time from the Working Hour.
  • 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)

Daily Payment Formula for Overtime Payroll in Excel

  • 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.

Employ MIN Function to Determine Overtime Payroll in Excel

  • 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.

MIN Function to Calculate Regular Time for Oertime Payroll Formula

Here, in the MIN function, I selected Cell E4 as number1 and Cell F8 as number2. The formula will return the smallest number among these numbers. I used Absolute Cell Reference for E4 so that it does not change while using Autofill to copy the formula.
  • 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.

Now, the formula subtracts the value in Cell G8 from the value in Cell F8.
  • 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.

Apply MAX Function to Find Overtime Payroll with Formula in Excel

  • 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.

Now, the formula subtracts the value in Cell G8 from the value in Cell F8.
  • 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.

Calculate Overtime Payroll in Excel Considering Lunch Break

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.

Using IF Function in Formula to Calculate Overtime Payroll in Excel Considering Lunch Break

🔎 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.

How to Calculate Conditional Overtime in Excel

  • 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.

Here, in the IF function, I selected F8>$E$4 as logical_test, F8-$E$4 as value_if_true, and 0 as value_if_false. The formula will check if the value in cell F8 is greater than the value in cell E4. If the logical_test is true then the formula will return F8-$E$4. Otherwise, it will return 0.
  • 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.

Now, in the IF function, I selected G8>1 as logical_test, G8 as value_if_true, and 0 as value_if_false. The formula will check if the Overtime is greater than 1 hour. If the logical_test is True then the formula will return the Overtime as Conditional Overtime. Otherwise, it will return 0.
  • 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.

Practice Sheet for Excel Payroll Overtime Formula


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.


Related Articles

Mashhura Jahan

Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo