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

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

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