How to Calculate Overtime Percentage in Excel (3 Quick Methods)

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.

Dataset 1


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.

How to Calculate Overtime Percentage in Excel Using TIME Function

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

How to Calculate Overtime Percentage in Excel Using TIME Function

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

How to Calculate Overtime Percentage in Excel Using TIME Function

Finally, the result should look like the following.

Using TIME Function

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

How to Calculate Overtime Percentage in Excel Using IF Function

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

How to Calculate Overtime Percentage in Excel Using IF Function

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

=G5/TIME(E5,0,0)

How to Calculate Overtime Percentage in Excel Using IF Function

Eventually, the result appears like the following.

Using IF Function

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

How to Calculate Overtime Percentage in Excel Using MAX and TIME Function

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

How to Calculate Overtime Percentage in Excel Using MAX and TIME Function

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

=G5/TIME(E5,0,0)

How to Calculate Overtime Percentage in Excel Using MAX and TIME Function

Finally, the result appears in the picture shown below.

Using MAX and TIME Function

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.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo