Eshrak Kader
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.
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 minute and second arguments.
- 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 the logical_test argument.
- This expression checks if the Work Hour (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 number2 argument. The 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.
