Frequently, you have to calculate overtime for tracking the regular and extra working time of your employees. Luckily, you can compute the overtime in Excel quickly. In this article, I’ll present to you 4 methods which mainly contain Excel formula to calculate overtime over 8 hours.

**Table of Contents**hide

## Download Practice Workbook

## Methods of Calculating Overtime over 8 Hours Using Excel Formula

Let’s be introduced with the following daily employee timesheet where the **Starting **and **Ending Time** are given along with other necessary information. Now we have to find overtime over **8** hours/day.

Before going to the methods, I would like to inform you that the first two methods treat overtime in **h:mm** format. And the rest methods calculate the overtime in decimal hours.

### 1. Applying the TIME Function to Find Overtime over 8 Hours in Excel

At the outset, you’ll see how we can calculate overtime using the **TIME** function quickly. The **TIME **function is a built-in **Date and Time** function in Excel that returns a decimal number of a particular time. More importantly, it is helpful if you want to use the function inside another formula.

However, we have to follow two simple steps to get overtime.

__Step 1:__

First of all, you need to find the hours worked by the employee. For determining this, just use the following formula.

`=E11-D11`

Here, **E11** is the starting cell of the ending time and **D11 **is the starting cell of the starting time.

__Step 2:__

Now, you need to utilize the **TIME** function as shown in the below formula.

`=F11-TIME(8,0,0)`

Here. **F11 **is the value of hours worked.

In the above formula, I utilized the **TIME **function to assemble the overtime i.e. over **8** hours/day.

So, when I subtract the specific time from the total hours worked by any employee, I’ll get the extra hours of working from the total working hours.

*Note:** here, the overtime is in h:mm format. You can fix the format using the Format Cells option (the keyboard shortcut is Ctrl + 1).*

**Read More: How to Add Time in Excel Over 24 Hours (4 ways)**

### 2. Applying TIME & IF Functions to Get Conditional Overtime

Let’s say you want to find the conditional overtime (OT) that meets certain criteria. For example, you want to count it as overtime if it exceeds 1 hour.

In such a situation, just use the following formula.

`=IF(E11-TIME(8,0,0)>=TIME(1,0,0),E11-TIME(8,0,0),0)`

Here, E11 is the starting cell of hours worked by the employee.

While explaining the formula, I can say that I assigned **E11-TIME(8,0,0)>=TIME(1,0,0) **as the ** logical_test** argument of the popular

**IF**function to fix the criteria of the overtime exceeding 1 hour. Later, I utilized

**E11-TIME(8,0,0)**syntax to get the amount of overtime if it fulfills the criteria; otherwise, it’ll return

**0**.

If you look closely at the below screenshot, you’ll get the output of **G14** and **G18 **as **0**. As the overtime is **0:30 **and **0:55 **respectively which are less than **1** hour. That’s why the conditional overtime is **0**.

**Read More: Excel Formula To Calculate Time Worked**

**Similar Readings:**

**[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)****How to Subtract Time in Excel (7 Quick Methods)****Calculate Total Hours in Excel (9 Easy Methods)****How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)****Calculate Turnaround Time in Excel (4 Ways)**

### 3. Using the MIN Function to Find Overtime over 8 Hours in Excel

Unlike the above two methods, we’ll calculate the overtime in decimal hours. Because the **MIN** function doesn’t work properly in** h:mm** format.

Let’s find the overtime by following the easiest 3 steps.

__Step 1:__

Initially, you have to find the hours worked by using the following formula.

`=(D11-C11)*24`

Here, **E11** is the starting cell of the ending time and **D11 **is the starting cell of the starting time.

More importantly, we need to multiply the output by **24** for getting the hours in decimal values as Excel treats the time as a portion of a day.

__Step 2:__

Now, we need to calculate the regular time utilizing the **MIN **function. So, the formula will be-

`=MIN(8,E11)`

In the above formula, the **MIN **function returns **8** hours, if the hours worked is equal or greater than 8 else it returns the value of hours worked.

__Step 3:__

Finally, we have to subtract the regular time from the hours worked as shown in the below formula.

`=E11-F11`

Here, **E11 **is the starting cell of the hours worked and the **F11 **is the starting cell of regular time.

Thus we can easily find the overtime over 8 hours such as the following picture shows.

**Read More:** **Excel Timesheet Formula with Lunch Break (3 Examples)**

### 4. Using the MAX Function

Furthermore, if you want to compute overtime after 8 hours of applying the **MAX** function, you may use the following formula.

`=MAX(0,E11-F11)`

Here, the **MAX **function returns **0 **if the output of subtraction is **0**. Else it returns the overtime over **8** hours in decimal hours.

**Related Content:** **Excel Formula for Overtime over 40 Hours [with Free Template]**

## Things to Remember

- Often you may get
**#VALUE!**error while subtracting the two time values if those are not in the right format. - Consider the format of overtime (e.g.
**h:mm**or decimal hours) while selecting the different methods.

## Conclusion

In short, this is how you can calculate the overtime over 8 hours utilizing the Excel formula. Also, you may measure extra payment for overtime as well as overtime. I strongly believe that this article will articulate calculation methods. If you have any queries or suggestions, please let me know in the comments section below.