# How to Calculate Payroll Overtime with Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

## Calculate Payroll Overtime with Formula in Excel: 4 Ideal Examples

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.

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.

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

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.

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

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

• 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 Make Payroll in Excel

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

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.

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

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.

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

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

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF