How to Calculate Overtime Percentage in Excel (3 Methods)

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:

1. Select a cell.
2. Calculate the overtime by entering the formula: =F5-TIME(E5,0,0)
   Here, the F5 cell refers to the Work Hour and the E5 cell represents the hour argument.
3.  Select another cell.
4. 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.
   
5. Select the cell where you have calculated the overtime percentage.
6. Select Home tab > Number group > Percentage.
   This will show the output in percentage format.
   
7. 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:

1. Select a cell.
2. Calculate overtime hours by using the formula below: =IF(F5>TIME(E5,0,0),F5-TIME(E5,0,0),0)
   Here, F5 is Work Hour and E5 is Regular Time.
   
3. Select another cell.
4. Calculate overtime percentage by inserting the formula: =G5/TIME(E5,0,0)
   Here, G5 is Overtime and E5 is Regular Time.
   
5. Select the cell where you have calculated the overtime percentage.
6. Go to Home tab > Number group > Percentage.
   
7. 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:

1. Select a cell.
2. Insert the formula below to calculate overtime: =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.
3. Select another cell.
4. Calculate overtime percentage by inserting the formula: =G5/TIME(E5,0,0)
   Here, G5 is Overtime and E5 is Regular Time.
   
5. Select the cell where you have calculated the overtime percentage.
6. Go to Home tab > Number group > Percentage.
   
7. 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).

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