Calculating overtime percentage means finding out how many extra hours of work are done by a person after the regular work shift is over. To ensures the wage distribution among employees in a company, we often need to calculate overtime percentage in Excel.

In this Excel tutorial, you learn how to calculate overtime percentage in Excel using different Excel functions.

The following image explains how to calculate overtime percentage after calculating overtime. Here, you first have to calculate overtime and then divide the overtime with regular time and change the formatting to percentage.

## 3 Methods to Calculate Overtime Percentage in Excel

To calculate overtime percentage, this article deals with three different approaches. We have applied various formulas by using one or multiple functions such as **TIME**, **IF**, and **MAX** for calculating overtime percentages.

Here are three different methods to calculate overtime in Excel:

### Using TIME Function

You can calculate overtime percentage using Excelâ€™s built-in **TIME** function. This method offers a direct approach to calculate overtime percentage but it can not handle negative overtime percentage.

To calculate overtime percentage using the **TIME** function, follow the steps below:

- Select a cell.
- Calculate the overtime by entering the formula:
Here, the`=F5-TIME(E5,0,0)`

**F5**cell refers to the Work Hour and the**E5**cell represents the hour argument. - Â Select another cell.
- Calculate the Overtime Percentage by inserting the formula:
`=G5/TIME(E5,0,0)`

In the above formula, the

**G5**cell indicates the Overtime**,**while the**E5**cell refers to the Regular Time in the time format.

- Select the cell where you have calculated the overtime percentage.
- Select
**Home**tab >**Number**group >**Percentage**.

This will show the output in percentage format.

- Finally, drag down the
**Fill Handle**icon and the result will appear like the following:

**Note:** Here, the Work Hour and the Overtime columns are in **h:mm **format. You change the format using the **Format Cells** option by pressing **CTRL + 1**.

### Using the Combination of IF and TIME Function

Calculating overtime percentage with the combination of **IF**Â and **Time **successfully handles negative overtime by using the **IF** condition. In contrast, using only the **Time** function gives an error when negative overtime occurs.

The steps to calculate overtime percentage with the combination of **IF** and **TIME** functions are given below:

- Select a cell.
- Calculate overtime hours by using the formula below:
Here,`=IF(F5>TIME(E5,0,0),F5-TIME(E5,0,0),0)`

**F5**is Work Hour and**E5**is Regular Time.

- Select another cell.
- Calculate overtime percentage by inserting the formula:
Here,`=G5/TIME(E5,0,0)`

**G5**is Overtime and**E5**is Regular Time.

- Select the cell where you have calculated the overtime percentage.
- Go to
**Â Home**tab >**Number**group >**Percentage**.

- Finally, drag down the
**Fill Handle**icon and the overtime value will appear in percentages like the following:

### Using MAX and TIME Function

Using** MAX** and** TIME** functions together ensures that negative overtime is treated as zero without the need for additional** IF** conditions, giving a more concise and efficient solution.

To calculate overtime percentage using the combination of **MAX** and **Time** functions, follow the steps below:

- Select a cell.
- Insert the formula below to calculate overtime:
Here,`=MAX(0, F5-TIME(E5,0,0))`

**0**is the**number1**argument, and**F5-TIME(E5,0,0)**is**number2**argument. The**MAX**function compares the two values and returns the largest value among them. - Select another cell.
- Calculate overtime percentage by inserting the formula:
Here,`=G5/TIME(E5,0,0)`

**G5**is Overtime and**E5**is Regular Time.

- Select the cell where you have calculated the overtime percentage.
- Go to
**Â Home**tab >**Number**group >**Percentage**.

- Finally, drag down the
**Fill Handle**icon.

The overtime percentage values appear as shown below:

**Download Practice Workbook**

## Conclusion

This Excel tutorial showed 3 methods to calculate overtime percentage in Excel. You can select any of the methods based on your requirements. To conclude, we hope this article helped you understand how to calculate overtime percentage in Excel. If you have any queries, please leave a comment below.

## Frequently Asked Question

### How do you add 3 hours to time in Excel?

In Excel, you can add 3 hours to a given time using a simple formula. Let’s say you have a time value in cell A1, and you want to add 3 hours to it. You can use the following formula: ** =A1 + TIME(3, 0, 0)
**This formula uses the

**TIME**function to add 3 hours (3 hours, 0 minutes, and 0 seconds) to the time value in cell A1. Adjust the cell reference accordingly based on where your original time value is located.

### How do you insert the hour in Excel?

To insert the current hour in Excel, you can use the **NOW()** function along with the **TEXT** function to extract and format the hour. Here’s an example:

`=TEXT(NOW(), "hh")`

This formula uses the **NOW()** function to get the current date and time and the** TEXT()** function to format it as a two-digit hour. The “hh” format code specifies that Excel should display the hour as a two-digit number (e.g., 01, 02, …, 12).

### How to handle varied hourly rates when calculating overtime percentage in Excel?

To handle varied hourly rates when calculating overtime percentage you have to follow the steps below:

- Calculate regular wages for each employee by multiplying regular hours worked by their respective hourly rates.
- Determine overtime wages: (Total hours – Regular hours) * Overtime hourly rate
- Sum regular and overtime wages for total wages
- Calculate overtime percentage: (Overtime wages / Total wages) * 100
- Adjust cell references in the formulas to match your Excel layout.

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