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.

**Table of Contents**hide

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

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

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

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

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

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

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

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

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

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