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 *Date*s. 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 U****se ****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.

`=(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`

âž¤ 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**).

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

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

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

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

`=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**.

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

`=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.

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

`=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.

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

`=E7-SUM(F7:G7)`

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.

`=SUM(E9:E13)`

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

`=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**.

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

`=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**.

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 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**