How to Calculate Overtime Percentage in Excel (3 Methods)

The following image explains how to calculate overtime percentage after calculating overtime. We’ll 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

Method 1 – Using the TIME Function

• Select G5.
• Calculate the overtime by entering the formula: =F5-TIME(E5,0,0)
  The F5 cell refers to the Work Hour and the E5 cell represents the hour argument.
• Select H5.
• Calculate the Overtime Percentage by inserting the formula: =G5/TIME(E5,0,0)
  The G5 cell indicates the Overtime, while the E5 cell refers to the Regular Time in the time format.
  
• Select the cell where you have calculated the overtime percentage.
• Go to the Home tab and click on Percentage or select the Percentage option on the drop-down.
  
• Drag down the Fill Handle icon and the result will appear like the following:
  

Note: The Work Hour and the Overtime columns are in the h:mm format. You can change the format using the Format Cells option by pressing Ctrl + 1.

Method 2 – Using the Combination of IF and TIME Functions

• Select G5.
• Calculate overtime hours by using the formula below: =IF(F5>TIME(E5,0,0),F5-TIME(E5,0,0),0)
  F5 is Work Hour and E5 is Regular Time.
  
• Select H5.
• Calculate overtime percentage by inserting the formula: =G5/TIME(E5,0,0)
  G5 is Overtime and E5 is Regular Time.
  
• Select the cell where you have calculated the overtime percentage.
• Go to the Home tab and select Percentage.
  
• Drag down the Fill Handle icon and the overtime value will appear in percentages like the following:

Method 3 – Using MAX and TIME Functions

• Select G5.
• Insert the formula below to calculate overtime: =MAX(0, F5-TIME(E5,0,0))
  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.
• Select H5.
• Calculate the overtime percentage by inserting the formula: =G5/TIME(E5,0,0)
  G5 is Overtime and E5 is Regular Time.
  
• Select the cell where you have calculated the overtime percentage.
• Go to the Home tab and select Percentage.
  
• Drag down the Fill Handle icon.

Download the Practice Workbook

Frequently Asked Question

How do you add 3 hours to time 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