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.

**Table of Contents**hide

**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 **1 ^{st}** 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 U****se ****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`

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

**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, 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 **1 ^{st} IF** function performing the

**Time Worked hours > 8**test.

**E7<=12; **is the **2 ^{nd} logical_test** if the

**1**results in

^{st }logical_test**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.

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

**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 appear 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 **ENTER** key and Dragging the **Fill Handle** gives you the **Double Time** values similar to the picture below.

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

**Similar Readings**

**How to Calculate Cycle Time in Excel (7 Examples)****[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)****Subtract Minutes from Time in Excel (7 Methods)****How to Calculate Time Difference Between AM and PM in Excel****Calculate Percentage of Time in Excel (4 Suitable Examples)**

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

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

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

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

**How to Subtract Date and Time in Excel (6 Easy Ways)****Calculate Elapsed Time Between Two Dates in Excel (5 Methods)****How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)****Calculate Average Response Time in Excel (4 Methods)****How to Add Time in Excel Over 24 Hours (4 ways)****Calculate Production per Hour in Excel (4 Ways)****How to Calculate Average Handling Time in Excel (2 Easy Ways)****Subtract Date and Time in Excel (6 Easy Ways)**