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:

- Select a blank cell and use the formula:

`=(E4-D4)*24`

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

- To convert into
**Number**format, select your data, then select**Number**format from the**Number**group.

- To calculate overtime, go to the G column, select the
**Number**format for the data cells, and insert the formula:

`=F4-8`

- 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:

- Select cell
**F4**. - Insert the formula:

`=IF(((E4-D4)*24) >8,((E4-D4)*24)-8,"0")`

- 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:

- Select a blank cell
**F11**and use the formula:

`=E11-D11`

- 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:

- Use the blank cell and insert the formula:

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

- 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:

- Format the F column the same way as in the instructions just for the TIME function above.
- Select a blank cell in the next column, such as G11.
- Apply the formula:

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

- 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:

- Select the cell
**E11**and insert the formula:

`=(D11-C11)*24`

- 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:

- Select a black cell (such as F4) and insert the formula:

`=E4-MIN(8,E4)`

- 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:

- Select a blank cell G11 and enter the formula:

`=MAX(0,E11-F11)`

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

**Download Practice Workbook**

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

## Frequently Asked Questions

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

- Select a blank cell.
- Go to
**Home**>**Number**group >**Number Format**icon. - 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**

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 Shimantohi 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

Hello

KIMHONGThanks 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 returns0 minutes. If it’s15 minutes or more, it’s considered as30 minutes.You can achieve the goal by developing a formula using the

IFandTIMEfunctions. Follow these steps:Hopefully, the idea will help. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Good day.

Please help with this calculation.

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.

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

please suggest

Hi

ManishaIt 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

NiloyExcelDemyTime 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(B2Formula to calculate the total working hours:

=C2+C3Formula to calculate the overtime,

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

ExcelDemy