Frequently, you have to calculate overtime to track 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 formulas to calculate overtime over 8 hours.

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 hours worked and overtime using an Excel formula containing the **TIME** function. 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. To determine 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:** Calculate Overtime over 40 Hours Using Excel Formula

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

Letâ€™s say you want to find the conditional overtime (OT) that meets certain criteria. you can calculate the Overtime in hours using the Excel **IF** function with the **TIME** function. 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 IF function**to fix the criteria of 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.

## 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 overtime over 8 hours as the following picture shows.

## 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. Otherwise, it returns the overtime over 8 hours in decimal hours.

## Things to Remember

- Often you may get
**#VALUE!**error in ExcelÂ 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.

**Download Practice Workbook**

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

Hi Dr. Abdul Kader,

Thank you for taking the time to write this lesson. I have been trying to make a similar timecard for a while now. This really helped.

Best Regards,

Mike Longoria

Hello

Mike Longoria,You are most welcome.

Regards

ExcelDemyThanks for the formula’s

Wondering what the formula would be to add a collumn for Double time for anything above 12 hours a day

Greg

Hello

GregThanks for your nice words. Your appreciation means a lot to us. You wanted to know the formula for adding a Double time column for anything

above 12 hoursdaily.I am delighted to inform you that I have developed an Excel Formula using the

IFandTIMEfunctions to fulfil your requirements.Follow these steps:Step 1: Select cellG11=> Insert the following formula.Step 2: HitEnterto see the result, like the image below.Step 3: Hover over the cursor on the right button corner of cellG11to see theFill Handleicon.Step 4: Drag theFill Handleicon to cellG18to copy down the formula.Hopefully, this idea will help you reach your goal. Good luck!

Regards

Lutfor Rahman Shimanto