Almost in every office environment nowadays we have to keep the working hours record of the employees. It is necessary when we want to know how many hours they have worked so that we can prepare the salary for the individual employees. Working overtime means that a particular employee should get extra payments for the extra hours. So it is quite important for us to keep track of the overtime of the employees. In this article, we will discuss **4** simple ways to prepare an Excel formula for overtime over than 8 hours. We will use Excel functions such as **TIME**, **TIME** &** IF**, **MIN,** and **MAX**. You can take a glance at the overview snapshot below to know the **Methods** and **Outcomes** of this article.

## 📁 **Download Excel File**

Download the practice file from here.

## Definition of Overtime in Work?

When an employee works longer than his regularly allocated working hours, it is referred to as **Overtime** in work. The generic meaning of **Overtime** simply refers to hours an employee does outside of the usual workday. We also frequently use **Overtime** to describe the pay for such work. So if an employee works more than the usual hours his/her extra time, as well as the extra payment, can be characterized as **Overtime**.

## Standard Working Hours And Overtime Calculation Formula

In every company, there is a specific time frame when the employees work. This time frame is the Standard working hours. It may vary from company to company but the basic idea holds everywhere. If such a situation arises when the employees need to work more than their usual hours then they are working Overtime. So the Overtime calculation formula is as follows.

**Overtime= Extra working hours-Regular working hours**

So we basically deduct the **Regular working hours** from the **Extra working** **hours** to get the **Overtime**.

**Learn to Create Excel Formula for Overtime Over 8 Hours with These 4 Methods**

In this article, we will discuss **4 **ways to prepare an Excel formula for overtime over 8 hours. Let’s assume that we have a dataset of the **Employee Work Record** of a company. We know the **Entry Time** & **Exit Time** of each individual person. At this moment we are trying to keep the **Overtime** record so that we can use that to prepare the salary sheet at the end of the month. In the following sections, we will discuss the methods briefly. The sample dataset to prepare an Excel formula for overtime over 8 hours is as follows.

**method**

### 1. Applying TIME Function

In this section, we will apply **TIME** function for creating an Excel formula for overtime over 8 hours.

This function gives us the decimal number for a specific time. The **syntax** of this function is **TIME(hour, minute, second)**. So if we insert the hour, minute, and second as arguments this function will return the particular time. We can use this function if we want to get a specific time as a return value. So the steps for using this method are as follows.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Firstly we go to the cell
**F6**formula box and write this formula.

**=E6-TIME(8,0,0)**

- Here
**TIME(8,0,0)**returns the time as**8:00**.

- Following that we use the
**Fill Handle**tool to drag the formula.

- Finally, we get our result in column
**F**.

**📕 Read More: ****How to Calculate Turnaround Time in Excel Excluding Weekends**

**method**

### 2. Applying TIME & IF Functions to Apply Conditions

In this section, we will discuss how we can use the **TIME** & **IF** functions together to create an Excel formula for overtime over than 8 hours. The syntax for the **IF** function is **IF(logical_test, [value_if_true], [value_if_false])**. So this function is useful when we want to do a specific task depending on some logical test. Now the steps for using this method are as follows.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Firstly we go to the cell
**F6**formula box and write this formula.

**=IF(E6-TIME(8,0,0)>=TIME(1,0,0),E6-TIME(8,0,0),0)**

**🔨**** Formula Breakdown**

👉** E6-TIME(8,0,0)>=TIME(1,0,0) **here are the criteria that we want to check.

👉 If the result is true then the return will be **E6-TIME(8,0,0)**.

👉 Otherwise the return will be **0**.

- Now we use the
**Fill Handle**tool to drag the formula. - Finally, we get the result in column
**F**.

**📕 Read More: ****7 Examples to Calculate Duration of Time in Excel**

**method**

### 3. Applying MIN Function

In this section, we will discuss how we can use the **MIN** function to create an Excel formula for overtime over than 8 hours. The syntax for the **MIN** function is **MIN(number1, [number2], …)**. This function returns us the minimum value from a given data range. So if we want to know the lowest value from a set of values we can use this function. So the steps for using this method are as follows.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Firstly we go to the cell
**F6**formula box and type our formula.

**=MIN(8,E6)**

- This function returns the minimum value among the 2 values.

- Now we write this formula in cell
**G6**to**get the Overtime.**

**=E6-F6**

- After that, we apply the
**Fill Handle**tool to copy the formula in columns**F**&**G**. - Finally, we will get our result in column
**G**.

**📕 Read More: ****Calculate Hours Between Two Dates and Times in Excel Excluding Weekends**

**method**

### 4. Using MAX Function

In our final section here we will use the **MAX** function to create an Excel formula for overtime over than 8 hours. The syntax for the MAX function is** MAX(number1, [number2], …)**. This function returns us the highest value among 2 or more numbers. So it is very handy when we want to know the highest value in a data range. So the steps for using this method are as follows.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Firstly we go to the cell
**G6**formula box and type this formula.

**=MAX(0,E6-F6)**

- This will return the highest value among the 2 numbers.

- Now we employ the
**Fill Handle**tool for dragging the formula. - Finally, we get our result in column
**G**.

**📕 Read More: ****5 Ways to Calculate Total Hours Worked in a Week in Excel**

## How to Calculate Overtime Excluding Lunch Time in Excel

In this section, we will apply the **MOD** function to calculate the overtime excluding lunch Time in Excel. The syntax for this function is **MOD(number, divisor)**. This function returns us the remainder after dividing by the divisor. So the steps for using this method are as follows.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Firstly we go to the cell
**G6**formula box and put this formula.

**=(MOD(F6-C6,1)-MOD(E6-D6,1))-TIME(8,0,0)**

- Here
**(MOD(F6-C6,1)-MOD(E6-D6,1))**returns us the net total work time. - We deduct the regular work time given by
**TIME(8,0,0)**to get the overtime.

- Following that we apply the
**Fill Handle**tool to drag the formula. - Finally, we get our result in column
**G**.

**📕 Read More: ****4 Examples of Timesheet Formula with Lunch Break in Excel**

## Common Mistakes to Avoid in Overtime Calculation

When we are trying to properly calculate the overtime of the employees we have to be careful about some usual mistakes that we can make. Here we will discuss some common mistakes that we need to be wary of.

- The number of non-discretionary bonuses, shift differentials, and some other forms of remuneration is all part of an employee’s regular rate of pay in addition to their hourly wage. As a result, if the employee receives these additional forms of payment, we must take them into account when calculating their normal rate of pay.
- We must not assume that incentives are optional so that we can deduct them from the standard rate of pay.
- We must prorate a non-discretionary bonus over the whole bonus period when an employee earns it over more than one workweek.
- We should not forget to take into account different hourly rates.
- We should not misuse the variable workweek approach.
- We need to estimate the regular rate of pay for tipped workers properly.

**📄**** Important Notes**

🖊️ When we use Excel functions we need to be careful about the function arguments

🖊️ We have to use the **Custom** data type as **h:mm** to properly calculate our result.

**📝**** Takeaways from This Article**

📌 We can use **TIME** function for preparing an Excel formula for overtime over than 8 hours.

📌 We can also use both the **TIME** & **IF** functions together to prepare an Excel formula for overtime over than 8 hours.

📌 **MIN** function is another way to do this task.

📌 Also we can employ the** MAX function** to prepare an Excel formula for overtime over than 8 hours.

**Conclusion**

In this article, we have learned **4** different ways to prepare an Excel formula for overtime over than 8 hours. We can use the **TIME**, **TIME **& **IF**, **MIN**, and** MAX** functions to do this task. Users can use any of the methods they like and feel comfortable with. If you want to ask any questions about this article feel free to comment in our comment box. The writer will try to answer them. But if you are looking for more Excel-related problems and solutions you can always visit our website **www.Excelden.com**.

## Related Articles

**4 Easy Ways to Calculate Percentage of Time in Excel****How to Calculate Average Response Time in Excel****9 Tricks to Calculate Time Difference Between AM and PM in Excel****2 Ways to Calculate Travel Time Between Two Cities in Excel**