Looking for ways to calculate overtime percentage? Then, you’ve come to the right place. Calculating overtime percentage is commonly used to track the regular and extra working time of the employees. With this in mind, this article provides 3 quick methods on how to calculate overtime percentage in Excel.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook from the link below.

**What Is Overtime Percentage?**

Overtime percentage is the overtime hours divided by the regular hours that an employee works. Hence, the arithmetic formula to calculate the overtime percentage is as follows.

`Overtime % = (Overtime Hours / Regular Hours) x 100%`

**3 Methods to Calculate Overtime Percentage in Excel**

Now, let’s consider the following **Employee Time Card **in the **B4:E12 **cells. Here, the dataset shows the **Starting Time**, the **Ending Time**, and the **Regular Time**.

So, let’s explore these methods step by step.

**1. ****Using TIME Function to ****Calculate Overtime Percentage **

We can calculate overtime percentage using Excel’s built-in **TIME** **function** which returns a decimal value of the time. So, just follow the steps shown below.

__Steps:__

- Firstly, insert a column with the heading
**Work Hour**and paste the formula given below.

`=D5-C5`

Here, the **C5** cell refers to *Starting Time* while the **D5** cell indicates the *Ending Time*.

- Now, drag the
**Fill Handle**tool to copy the formula in the cells below.

- Secondly, calculate the
**Overtime**by entering the formula in the**G5**cell as shown below.

`=F5-TIME(E5,0,0)`

In the above expression, the **F5** cell refers to the **Work Hour**. Additionally, the **E5 **cell represents the ** hour** argument while the zeros refer to the

**and**

*minute***arguments.**

*second*- Thirdly, compute the
**Overtime Percentage**as shown in the picture below.

`=G5/TIME(E5,0,0)`

In the above formula, the **G5** cell indicates the **Overtime,** while the **E5** cell points to the **Regular Time **in the time format.

Finally, the result should look 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**.**Read More:** **Excel Formula to Calculate Overtime and Double Time (3 Ways)**

**2. Calculating Overtime Percentage with IF Function**

Another way to calculate the overtime percentage is using the popular **IF function****. **Now, let’s see the process step-by-step.

__Steps:__

- At the very beginning, obtain the
**Work Hour**by subtracting the value of the**C5**cell from the**D5**cell.

`=D5-C5`

- Secondly, we obtain the
**Overtime**by typing in the expression below.

`=IF(F5>TIME(E5,0,0),F5-TIME(E5,0,0),0)`

**Formula Breakdown:**

- In the formula for
**Overtime**, the**F5>TIME(E5,0,0)**expression is theargument.*logical_test* - This expression checks if the
**Work Hou**r (**F5**) is greater than the**Regular Time**(**E5**). - Secondly,
**F5-TIME(E5,0,0)**is the**value_if_true**argument which returns the result of the subtraction if the logical test is**TRUE**. - Lastly,
**0**serves as the**value_if_false**argument which is returned if the logical test is**FALSE**.

- Next, we obtain the
**Overtime Percentage**dividing the**G5**cell by the**E5**cell in time format.

`=G5/TIME(E5,0,0)`

Eventually, the result appears like the following.

**Read More:** **How to Calculate Overtime Hours in Excel Using IF Function**

**3. Utilizing MAX with TIME Function to Calculate Overtime Percentage**

If using complex formulas isn’t your thing, then our next method uses the **MAX function**. As the name suggests, the **MAX** function returns the maximum value in a given set of values.

__Steps:__

- Similar to the previous methods, firstly, obtain the
**Work Hour**.

`=D5-C5`

- Next, get the
**Overtime**by entering the expression provided below.

`=MAX(0, F5-TIME(E5,0,0))`

Here, **0** is the ** number1** argument, and

**F5-TIME(E5,0,0)**is

**argument. The**

*number2***MAX**function compares the two values and returns the largest value among them.

- Then, we calculate the
**Overtime Percentage**dividing the**G5**cell by the**E5**cell.

`=G5/TIME(E5,0,0)`

Finally, the result appears in the picture shown below.

**Read More:** **How to Calculate Hours Worked and Overtime Using Excel Formula**

**Things to Remember**

- Firstly, you may get
**#VALUE!**error when subtracting two-time values if they are not in the proper format. - Secondly, you should change the time values to
**h:mm**format when performing calculations.

**Conclusion**

To conclude, I hope this article helped you understand how to calculate overtime percentage in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website **ExcelDemy**.