Excel Formula to Calculate Overtime and Double Time (3 Ways)

In Excel, it’s a common scenario where we use Excel Formula to calculate Overtime and Double Time. Calculating Overtime and Double Time is quite a bit tricky and subject to employee work agreements. In this article, we demonstrate ways to calculate overtime and double time following a standard working agreement.

Let’s say we have a dataset containing an employee’s Entering and Existing Time on certain Dates. We want to calculate the Overtime and Double Time for that employee.

Differentiate Between Overtime and Double Time

In general, a full-time employee works for 8 hours to call it a day of normal working hours. If an employee works more than 8 hours a day, there are typically two things to assign to the situation. The 1^st one is Overtime and the other one is Double Time.

Overtime is the count of working hours more than 8. It has a typical payment rate 1.5 times of the normal working hours. In certain states (i.e., California) of the USA, if the working hours exceed the 12-hour mark, the employees are subject to the Double Time working hour agreement. Therefore, he or she will receive an hourly working rate 2 times the normal working hours. The eligibility or payment of being an overtime or double-time worker may vary in different countries or cities.

How to Use Excel Formula to Calculate Overtime and Double Time: 3 Ways

Method 1: Using IF and MIN Excel Formula to Calculate Overtime and Double Time

From the dataset, we have an Entering and Existing Time for each date. We will use Excel functions and formulas to calculate the hours worked, Overtime, and double time for each working date.

First, we use the Arithmetic Operator Subtraction to count the hours.

However, Excel by default displays the day amount while working with time operations. Therefore, we multiply 24 (i.e., =(D7-C7)*24) with the resultant value in order to convert the day into hours.

➤ Type the following formula in any blank cell (i.e., E7) to calculate the hours worked.

➤ Now, Hit ENTER then Drag the Fill Handle to find the working hours for individual dates.

You see we calculate all the working hours for Ross Johnson. Nevertheless, we can’t find the Overtime and Double Time. Since we only calculate working hours, not any condition imposed, we won’t be able to find labeled Overtime or Double Time working hours. To find out sorted Overtime and Double Time, you have to follow the below steps.

Step 1: Firstly, you have to calculate the Normal Work (NW). Normal Work calculation ensures that employees do their required working hours. To calculate the normal working hours, Type the following formula in any adjacent blank cell (i.e., F7).

The IF function runs a logical_test (i.e., E7>8) and then displays 8 as the Normal Work hour if the test results in TRUE. Otherwise, the formula displays the Subtraction value between Exiting and Entering Time (i.e., D7-C7).

Step 2: Press ENTER then Drag the Fill Handle to display whether the employee fulfills the normal hours or not.

You can see employee Ross Johnson successfully fulfills the normal working hours in each working day.

Step 3: Now, to calculate the Overtime in hours, you need to use the Excel IF function for each working day. Write the below formula in any cell (i.e., G7).

E7>8; is the logical test for the 1^st IF function performing the Time Worked hours > 8.

E7<=12; is the 2^nd logical test if the 1^st logical test results in TRUE.

E7-8; value is displayed if the 2^nd logical test results in TRUE otherwise MIN(E7-8,4) value is displayed.

The MIN function displays the minimum value among E7-8 and 4. Because for above 12 hours working day, hours above 12 are subject to Double Time (DT). Overtime is allowed to be a maximum of 4 hours after the normal 8 working hours.

If the 1st logical test returns FALSE, the formula displays 0 hours as Overtime.

Step 4: After hitting the ENTER key and dragging the Fill Handle, the Overtime values will appear.

Step 5: Since some of the working hours are more than 12 (from the Time Worked column), we have to calculate the Double Time (DT). Write the following formula in any cell where you want to display the Double Time value (i.e., H7).

Inside the formula, the IF function runs a logical_test that passes the working hours to Subtraction, if the logical_test results TRUE otherwise displays 0.

Step 6: Use the ENTER key then Drag the Fill Handle to display the Double Time hours as shown in the following image.

Method 2: Combined MIN and SUM Functions to Calculate Overtime and Double Time

In the previous method, we used a conditional formula (i.e., IF and MIN) to calculate Overtime and Double Time. However, combined MIN and SUM functions can also calculate the Overtime and Double Time values abiding by the parameters we set in the Differentiating section of this article. For this method, we consider the Time Worked hours as a part of a dataset. Therefore, we are not repeating the Time Worked calculation.

Step 1: To find out the normal working hours, use the following formula in any adjacent cell (i.e., F7).

The MIN function returns the smallest numeric value among the provided values. So, if the working hours are more than 8 hours, the formula displays 8 as normal working hours.

Step 2: Following the formula insertion, Press ENTER then Drag the Fill Handle to see whether normal working hours are met or not.

Step 3: Normal working hours work as a criterion in Overtime calculation in this method. Again, the MIN function calculates the Overtime as depicted in the picture below.

E7-F7; is the subtracted value between Time Worked and Normal Work.

The smallest value among E7-F7 and 4 is considered as Overtime. Because on a working day, an employee can have a maximum of 4 hours Overtime. More than this is subjected to Double Time.

Step 4: After pressing ENTER, Drag the Fill Handle to show the Overtime hours.

Step 5: It’s obvious that Normal Work, Overtime, and Double Time constitute the total working hours. Just by subtracting the Normal Work and Overtime from Time Worked, we can simply get the Double Time.

The formula subtracts Normal Work and Overtime from Time Worked hours.

Step 6: Using the ENTER key and Dragging the Fill Handle gives you the Double Time values similar to the picture below.

Method 3: Excel IF and SUM Excel Formula to Calculate Overtime and Double Time

In companies, they consider hours more than 40 in a week of 5 working days as Overtime. And total working hours of more than 60 is considered Double Time. In order to determine overtime over 40 hours using the Excel formula, we are modifying our dataset as depicted in the below screenshot.

Step 1: First, we calculate the Time Worked values following Method 1 sequences. Then apply the SUM function to calculate the total working hours in a week.

Step 2: By pressing ENTER, you see the total working hours.

Step 3: In the Overtime column, use the below formula to find out hours that are more than 40 in a week. This number of hours is not Overtime according to the parameters.

SUM($E$9:E9); increases its cell reference up to E13 and sums all the hours.

SUM($E$9:E9)>40; logical_test that executes SUM($E$9:E9)-40 if TRUE is returned otherwise 0.

So, Ross Johnson has worked an extra 25 hours beyond his normal working hours.

Step 4: Paste the following formula in any Double Time column cell (i.e., G9).

This formula does the same thing as described in Step 2 except the parameter of 60 hours instead of 40.

At last, you get the Extra Time and Double Time hours.

Step 5: In Step 1, you see the Time Worked value is more than 65 and the total Extra Time value is 25. However, only 20 hours are allowed to be considered as Overtime. So, paste the following formula to find out the actual Overtime in G14.

Total working hours of more than 60 will have a minimum value among F13 and 20. This value insertion happens whether the logical_test (i.e., E14>60) returns TRUE or FALSE.

➤ The formula in G14 returns the actual Overtime hours. And you can see the Overtime value is 20 in the following picture.

➤ If you want to display the Double Time along with Time Worked and Overtime, type =G13 in the G14 cell.

Executing all the steps gives you weekly basis Overtime and Double Time. In the calculations, we consider the standard working hour parameters of Overtime and Double Time. If any company differs from these hour parameters, just put the values in the formulas.

Download Excel Workbook

Conclusion

In this article, we use multiple formulas to calculate Overtime and Double Time. In the formulas, we use the IF, MIN, and SUM functions. Hope these above-mentioned formulas fulfill your demand in the pursuit of calculating Overtime and Double Time. Comment, if you have further inquiries or have anything to add.

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