How to Calculate Overtime Percentage in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Calculate overtime 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.How to Calculate Overtime Percentage in Excel Using TIME Function
  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.
    Percentage overtime using time function
  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.
    Percentage overtime using time function
  7. Finally, drag down the Fill Handle icon and the result will appear like the following:
     Percentage overtime 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.

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.
    How to Calculate Overtime Percentage in Excel Using IF Function
  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.
    Overtime percentage using IF function
  5. Select the cell where you have calculated the overtime percentage.
  6. Go to Home tab > Number group > Percentage.
    Overtime percentage after converting percentage
  7. Finally, drag down the Fill Handle icon and the overtime value will appear in percentages like the following:

Using IF Function

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.How to Calculate Overtime Percentage in Excel Using MAX and TIME Function
  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.
    Overtime percentage using Max function
  5. Select the cell where you have calculated the overtime percentage.
  6. Go to Home tab > Number group > Percentage.
    Turning overtime into percentages
  7. Finally, drag down the Fill Handle icon.
    The overtime percentage values appear as shown below:

Using MAX and TIME Functions

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

How to handle varied hourly rates when calculating overtime percentage in Excel?

To handle varied hourly rates when calculating overtime percentage you have to follow the steps below:

  1. Calculate regular wages for each employee by multiplying regular hours worked by their respective hourly rates.
  2. Determine overtime wages: (Total hours – Regular hours) * Overtime hourly rate
  3. Sum regular and overtime wages for total wages
  4. Calculate overtime percentage: (Overtime wages / Total wages) * 100
  5. Adjust cell references in the formulas to match your Excel layout.



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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo