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.

Dataset-Calculate Overtime and Double Time


Download Excel Workbook


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 the situation. The 1st one is Overtime and the other one is Double Time.

Overtime is the count of working hours more than 8. And 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 hours 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 hour. The eligibility or payment of being overtime or double time worker may vary in different countries or cities.


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

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. Normally we calculate the working hours for each working date. We use the Arithmetic Operator Subtraction to count the hours.

=(D7-C7)

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.

=(D7-C7)*24

IF MIN formula-Calculate Overtime and Double Time

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

Fill handle

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

=IF(E7>8,8,D7-C7)

The IF formula runs a logical_test (i.e., E7>8) 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).

Formula insertion- Calculate Overtime and Double Time

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

Normal work

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

Step 3: Now, you have normal working hours, and you want to calculate the Overtime (OT) for each working day. Write the below formula in any cell (i.e., G7).

=IF(E7>8,IF(E7<=12,E7-8,MIN(E7-8,4)),0)

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

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

E7-8; value is displayed if the 2nd 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.

Overtime formula insertion

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

Overtime

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

=IF(E7>12,E7-12,0)

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.

Double time formula insertion

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

Overtime and Double time hours-Calculate Overtime and Double Time

Read More: How to Calculate Overtime over 40 Hours Using Excel Formula


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

In the previous method, we use 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).

=MIN(E7,8)

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.

IF SUM formula-Calculate Overtime and Double Time

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

normal working hours

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.

=MIN(E7-F7,4)

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.

Overtime calculation

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

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.

=E7-SUM(F7:G7)

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

Double time calculation

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

Double time

Read More: Excel formula to calculate hours worked & overtime [with template]


Similar Readings


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 replicate the situation, we modify our dataset as depicted in the below screenshot.

Dataset-Calculate Overtime and Double Time

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

=SUM(E9:E13)

Working hour sum-Calculate Overtime and Double Time

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

Total working hour

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.

=IF(SUM($E$9:E9)>40,SUM($E$9:E9)-40,0)

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.

Extra hours

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

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

=IF(SUM($E$9:E9)>60,SUM($E$9:E9)-60,0)

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

Double time calculation

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.

=IF(E14>60,MIN(F13,20),MIN(F13,20))

Total working hours 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.

Overtime hours

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

Overtime-Calculate Overtime and Double Time

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

Total worked, overtime, and double time-Calculate Overtime and Double Time

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.

Read More: Excel Formula To Calculate Time Worked


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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo