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 formulas in Excel. Different examples will be explained for your convenience.

**Table of Contents**hide

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

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

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

**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 Hour**s 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.

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

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

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

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.

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

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

**Download Practice Workbook**

You can download the practice workbook from here.

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