# 6 Best Excel Formula to Calculate Overtime Over 8 Hours

Calculating overtime is essential for company payroll or time-tracking purposes. In many jurisdictions, labor laws mandate that employees receive overtime pay when they work more than a certain number of hours in a day or week, typically beyond 8 hours in a day.

This Excel tutorial will show you how to calculate overtime over 8 hours.

Consider an employee timesheet containing starting and ending times. This dataset will be used to demonstrate six formulas for each employee.

## Using Simple Arithmetic Formula

Start with the basic formula to calculate overtime over 8 hours: (end time – start time) [in hours] – 8

By using a simple arithmetic formula in Excel, you can calculate overtime over 8 hours. But it returns decimals when you select Number format as the data type.

To determine the total working hours, follow the steps:

1. Select a blank cell and use the formula:
`=(E4-D4)*24`
2. Drag down the Fill Handle tool to copy the formula.
If you look carefully, the data for start and end times is in Time format. Addition and subtraction, and the arithmetic conversion on these values is calculated in days. That’s why the numeric value needs to be multiplied by 24 to get the amount of hours.
3. To convert into Number format, select your data, then select Number format from the Number group.
4. To calculate overtime, go to the G column, select the Number format for the data cells, and insert the formula:
`=F4-8`
5. Finally, drag down the Fill Handle tool to copy the formula.

The final result is that values in the G column are the number of hours (in decimals) of overtime.

## Using Excel IF Formula

One downside of the basic arithmetic method is that the formula will return negative values if a person works fewer hours, which might look odd. The IF function allows you to calculate extra time after a certain period and create branching logic. Keep in mind that you must select the Number format to get the hours in decimal.

To determine overtime with the Excel IF function:

1. Select cell F4.
2. Insert the formula:
`=IF(((E4-D4)*24) >8,((E4-D4)*24)-8,"0")`
3. Use the Fill Handle to copy the formula down to the other cells in the F column.

With this IF formula, the output will be the same as the arithmetic method if there is overtime to calculate, but the result will be 0 otherwise.

## Applying the TIME Function

The TIME function is a built-in Excel function categorized as a conversion function that returns the decimal number for a particular time. It can be used to perform arithmetic manipulation on time values or concatenate different values, but it can also be useful when calculating overtime. Follow these two basic  procedures:

### Step 1: Determine Total Working Hours

First, you need to find the hours worked by the employee:

1. Select a blank cell F11 and use the formula:
`=E11-D11`
2. Drag the Fill Handle up to copy the formula to the rest of the column.

Therefore, the total working hours for each employee are obtained.

### Step 2: Calculate Overtime

Now, you need to utilize the TIME function to figure out overtime over 8 hours. Here is how:

1. Use the blank cell and insert the formula:
`=F11-TIME(8,0,0)`
2. Drag down the Fill Handle tool.

As a result, overtime is calculated from the total working hours.

Note: The values need to be in the [h]:mm:ss format for these calculations to work properly. You can fix the format using the Format Cells option (the keyboard shortcut is Ctrl + 1).

## Combining TIME & IF Functions

You can also use the combination of IF and TIME functions to calculate overtime in Excel. The IF function allows logical statements.

For example, the IF statement can be useful if you want to count extra hours worked as overtime only if it exceeds 1 hour.

To calculate this conditional overtime with the Excel IF-TIME formula:

1. Format the F column the same way as in the instructions just for the TIME function above.
2. Select a blank cell in the next column, such as G11.
3. Apply the formula:
`=IF(F11-TIME(8,0,0)>=TIME(1,0,0),F11-TIME(8,0,0),0)`
4. Use the Fill Handle tool to copy the formula to the other cells in the column.

If you look closely at the image below, you’ll see the output of G7 and G11 as 0. The overtime is 0:30:00 and 0:55:00 respectively, which are less than 1 hour. That’s why the conditional overtime is 0:00:00.

## Using MIN Function

For calculating overtime over 8 hours, the use of the MIN function can be an excellent decision. While the MIN function functions with the h:mm:ss format used for TIME functions, it’s much more straightforward to use it with regular numbers.

Let’s find the overtime by following the three steps:

### Step 1: Calculate Working Hour

While subtracting the ending time from the starting time, Excel treats the time as a portion of a day. So, you need to multiply the output by 24 to get the hours in decimal values.

To calculate working hours:

1. Select the cell E11 and insert the formula:
`=(D11-C11)*24`
2. Use the Fill Handle tool to autofill the rest of the column.

Thus, working hours for each employee show up.

### Step 2: Calculate Overtime

Finally, by subtracting the regular time from the hours worked, you will obtain overtime over 8 hours. Here’s how:

1. Select a black cell (such as F4) and insert the formula:
`=E4-MIN(8,E4)`
2. Drag down the Fill Handle tool to copy the formula.

Therefore, you can calculate the overtime over 8 hours for each employee.

Note: Often you may get #VALUE! error in Excel while subtracting the two-time values if those are not in the right format. That’s why it’s best to put the calculation columns in a traditional Number format.

## Using MAX Function

If you want to compute overtime after 8 hours, the MAX function can be another way. The function will return decimals as well. For example:

1. Select a blank cell G11 and enter the formula:
`=MAX(0,E11-F11)`
2. Use the Fill Handle tool to copy the formula.

The MAX function returns 0 if the output of subtraction is 0. Otherwise, it returns the overtime in decimal hours. For the example sheet shown for this step, the MIN function has been used to cut the hour norm for people working for fewer than eight hours, resulting in a different overtime requirement (which is still at most eight hours).

## Conclusion

When working with time-based values, make sure to carefully consider what cell format you’re using. The Time format can be useful in setting the start and end dates, but you’ll need the custom [hh]:mm:ss format to display durations of time. For easy calculations, it can be best to use the basic Number format and manipulate the result to align with the number of hours.

### How do I add time greater than 24 hours in Excel?

To display the time as more than 24 hours:
1. Select a blank cell.
2. Go to Home > Number group > Number Format icon.
3. In the Format Cells dialog box, choose Custom in the Category list and [h]:mm:ss format in the type list. Finally, select OK.

<< Go Back to Overtime | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

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

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

Lutfor Rahman Shimanto Nov 29, 2023 at 1:12 PM

Hello Greg

Thanks 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 hours daily.

I am delighted to inform you that I have developed an Excel Formula using the IF and TIME functions to fulfil your requirements.

Step 1: Select cell G11 => Insert the following formula.

=IF(F11-TIME(12,0,0)<0,0,F11-TIME(12,0,0))

Step 2: Hit Enter to see the result, like the image below.

Step 3: Hover over the cursor on the right button corner of cell G11 to see the Fill Handle icon.

Step 4: Drag the Fill Handle icon to cell G18 to copy down the formula.

Regards
Lutfor Rahman Shimanto

3. hi can you help me with this calculation
working hour 11
time start 5:30 till ….
if OT exceeds 15minute count 30minute
If OT under 15minute count 0minute

Lutfor Rahman Shimanto Jan 17, 2024 at 1:42 PM

Hello KIMHONG

Thanks for sharing your query. In your case, an employee works 11 hours and starts working at 5:30 AM. If the overtime is between 0 and 15 minutes, you wanted a formula that returns 0 minutes. If it’s 15 minutes or more, it’s considered as 30 minutes.

You can achieve the goal by developing a formula using the IF and TIME functions. Follow these steps:

1. Select the intended cell.

2. Apply the following formula: `=IF((E3-TIME(5,30,0)-(11/24) > TIME(0, 15, 0)), TIME(0, 30, 0)*24, TIME(0, 0, 0))`

3. Drag the Fill Handle icon to copy the formula down.

Hopefully, the idea will help. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

4. Good day.

My working hours is from 8 till 16:30. Travel time and work time before and after working hours count as over time. So I must calculate Overtime travel and overtime work outside working hours. Public Holidays and Sundays is double overtime.

Regards
Johan Marais

• Hi, Mr. Marais, thanks for reaching out. The way I see it, your time starts when you start travelling to your workplace and the end time is when your work time is over. So, subtracting 8 and half hour from this period of time (end time – travel start time) will provide the overtime. Also, Sundays and Holidays will be double overtime. Using this idea, I developed the following formula:

`=IF(OR(B3="Sunday",C3="Yes"),2*((E3-D3)-TIME(8,30,0)),(E3-D3)-TIME(8,30,0))`

The image below is here for better clarification.

5. In time 9:32
Out Time 19:48
Total Supported ?
In Clock it is 10.16 min
but with formula – 10.27 min which wrong

Lutfor Rahman Shimanto Feb 25, 2024 at 6:38 PM

Hi Manisha

It is working fine for me and returning 10:16. Please give us more details about the formula and/or the dataset you are using.

Regards
Niloy
ExcelDemy

6. Time IN Time OUT Time IN Time OUT
9:00 AM 3:30 PM 9:00 PM 12:00 AM

how to overtime calculator

• Hello Minzameera,

Hope you are doing well. Here we calculated the overtime based on your criteria. We assumed that this time spans are for one person. If these are for different person you can modify the formula.

Formula ta calculate working hours: =(IF(B2

Formula to calculate the total working hours: =C2+C3

Formula to calculate the overtime, here we assumed the working hours are 8: =IF(D2>B6,D2-B6,”No Overtime”)

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF