**Mr. Liam Oliver **is a **Production Officer** who works in a renowned Can Making Company. Mr. Liam is responsible for controlling the production floor. Sometimes he has to perform Overtime, perhaps sometimes Double Time to meet the deadline of finished goods. But recently, he discovered that a respected Human Resource officer could not estimate actual working hours. This is the story of my colleague, Mr. Liam Oliver. In the meantime, He seeked my help. So, I intimidated him to calculate Overtime and Double time using a simple formula in Excel that regales him. In this article, I will briefly explain the calculation of Overtime and Double time using Formulas in Excel.

To construct the examples while calculating **Overtime** and **Double** **Time** containing formulas in Excel, we will use the **IF**, **MIN,** and **SUM** functions. The use of **VBA** **Macro** is also illustrated later in this article.

## ๐ Download Excel File

To practice please download the Excel file from the link below:

## Overtime Calculation Formula

**Overtime** is the extra hour of duty performed by an employee. Mainly Overtime is considerable after general duty hours. Furthermore, an employee earns generally **1.5x** times that of a general duty hour. Considering general duty hours of **8 hours** per day, If an employee performs **10 hours** of duty. Thus, deducting the general duty hours, we obtain the overtime of **2 hours**. So, the formula for Overtime calculation is as follows.

**Overtime=Total Performed Duty Hours โ General Duty Hours**

## Double Time Calculation Formula

Multi-national companies often have a policy that if a person works more than a recommended hour, then the rest of the hours is considered as **Double Time**. Companies generally pay **2x** times for those double-time hours. If an employee works for **14 hours** a day. Considering **8 hours** of general duty hours, the Overtime is **4 hours** and the rest** 2 hours** are considered as Double time. The general formula of **Double** **Time** calculation is as follows.

**Double Time = Total performed Duty Hours โ (General Duty Hours + Overtime)**

However, Some companies pay all the extra hours considering double time if an employee performs more than **12 hours**. I.E. If one performs **13 hours** in a day, the Company is bound to pay the employee double for an extra **5 hours**.

## Difference Between Overtime and Double Time

**Overtime** is the extra hours after **8 hours** of general duty hours. Meanwhile, **Double** **Time** is the extra duty hours after considering the sum of general duty hours and Overtime performed by an employee. If an employee works for **14 hours** a day. That means he performs **8 hours** of **G****eneral** **Duty** **Hours**, **4 hours** of **Overtime**, and the rest **2 hours** are considered as **Double time**.

## Learn to Calculate Overtime and Double Time Using Formula in Excel with These 5 Examples

In this article, we will explore **5 catchy examples** to Calculate Overtime and double time using the formula in Excel. Here we consider a dataset named **Weekly Timesheet of Mr. Liam Oliver** including 4 headings of **Date**, **Weekday**, **Entry**, and **Exit**. Attendance of the **1st week of 2023** is stored in the dataset. The dataset also has **5 columns** as well as **10 rows**. To construct the examples, we are going to expound on **IF**, **MIN**, and **SUM** functions as well as **VBA** **Macro**. So, letโs get started.

**method**

### 1. Use of IF Function to Calculate Overtime and Double Time After 8 Hours in Excel

**IF **function is a logical function that makes decisions based on a specific logic. Using the **IF** function formula one can calculate the overtime and double time after **8 hours** single-handedly in Excel. But first, we must learn the use and general **syntax** of the **IF** function as follows,

**=IF(logical_test, if_True, if_False)**

Please follow the required steps below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Initially, select a cell i.e.
**F6**under the**Duty Hour**heading to calculate the total duty hour. - Secondly, Insert the following formula in the
**F6**cell.

**=(E6-D6)*24**

- Thus, get the duty hour of
**9 hours**in the**F6**cell. Therefore, use the**Fill Handle**tool to auto-fill the range**F6:F10**, and obtain the duty hour for each day below the**Duty Hour**heading.

- Now, navigate the
**H6**cell and write down the formula containing the**IF**function.

**=IF(F6-G6<4, F6-G6,4)**

**๐จ Formula Breakdown**

๐ย Firstly, **F6-G6**ย means the deduction of **General Duty Hours** from** Duty Hour** performed in a day.

๐ย Secondly, the **IF** function acts based on logic. **F6-G6<4** implies that if the result of **F6-G6** is less than **4** then write the result achieved from **F6-G6**. Else write **4** in the **H6** cell.

- In the meantime, obtain the
**Overtime**of**1 hour**in the**H6**cell. Furthermore, to auto-fill the**H6:H10**range, use the**Fill Handle**tool.

- Afterward, select the
**I6**cell and input the formula containing the**IF**function.

**=IF(F6-12>0,F6-12,0)**

**๐จ Formula Breakdown**

๐ย Firstly, **F6-G6**ย means the deduction of **General Duty Hours** and **Overtime** from the **Duty Hour** performed in a day.

๐ย Secondly, the **IF** function acts based on logic. **F6-12<4** implies that if the result of **F6-12** is greater than **0**, then write the result achieved from **F6-12**. Else write **4** in the **H6** cell.

- Finally, obtain the Double time of
**0**hr in the I6 cell. Furthermore, to fill automatically the**H6:H10**range, use the**Fill Handle**tool. - Nevertheless, letโs check out the
**I8**cell where Mr. Liam performed**2 hr**of**Double****Time**on**Wednesday**. Prominently, Mr. Liamโs duty hour was almost**14 hours**.**8 hours**are considered**Normal General Duty**,**4 hours**of**Overtime,**and**2 hours**of**Double Time**.

**๐ Read More: ****4 ways to Create Excel Formula for Overtime Over 8 Hours**

**method**

### 2. Implement Excel MIN Function to Measure Overtime and Double Time

In the previous method we used the **IF** function, nevertheless using the **MIN** function will also lead us to obtain the **Overtime** and **Double** **Time**. **MIN** function generally takes the lowest value from selected data. Suppose there are two values of **5** and **8**. **MIN** function will pick **5**. So, letโs check out the **MIN** function as follows.

**=MIN(value1, value2)**

Please follow the necessary steps below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- To begin the method, measure the
**F6:F10**range according to the previous method. - Then, select a cell i.e.
**G6**to calculate general duty hours. - Secondly, Input the following formula in the
**G6**cell.

**=MIN(F6,8)**

- As you can see, we obtain the general duty of
**8 hrs**compared to the**F6**cell. The**MIN**function generally takes the lowest value from the selected dataset. - Further, use the
**Fill Handle**tool to fill the range**G6:G10**automatically.

- Again, to calculate overtime use the following formula in the
**H6**cell containing the**MIN**function.

**=MIN(F6-G4,4)**

- Therefore, get the
**1 hr**of**Overtime**in the**H6**cell that is calculated by deducting the**General Duty**from the**Performed Duty Hour**. - Likewise, fill the rest of the
**Overtime**column using the**Fill Handle**tool.

- Eventually, now we need to calculate the
**Double****time**. - So, input the following formula in the
**I6**cell containing the**IF**function.

**=IF(F6-12>0,FY-12,0)**

- In the end, obtain the
**Double****Time**in the**I6**cell. Use the**Fill****Handle**tool to fill the other cells of the**Double Time**column. - In the meantime, you may observe that
**Mr. Liam**performed**2 hours**of**Overtime**on**Wednesday**. He performed almost**14 hours**in total.

**๐ Read More: ****9 Tricks to Calculate Time Difference Between AM and PM in Excel**

**method**

### 3. Combination of MIN, IF and SUM Formula for Overtime and Double Time Calculation in Hours in Excel

The combination of **IF**, **MIN**, and **SUM** functions is an amazing way to measure the **Overtime** and **Double** **Time** in Excel. We are going to measure **General** **Duty** **Hours**, **Overtime**, and **Double** **Time** utilizing **MIN**, **IF** and **SUM** functions separately for each cell. Please follow the general **syntax** of the **SUM** function.

**=SUM(array)**

Please check the required steps.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly, measure the
**F6:F10**range following the above method. - Secondly, select a blank cell i.e.
**G6**to calculate**General Duty Hours**. - Thirdly, Input the following formula in the
**G6**cell associated with the**MIN**function.

**=MIN(F6,8)**

- In the meantime, we obtain the
**General Duty**of**8 hrs**compared to the**F6**cell. The**MIN**function generally takes the lowest value from the selected dataset. - Furthermore, use the
**Fill Handle**tool to fill the**General Duty**ย column automatically.

- Afterward, select the
**H6**cell and input the formula containing the**IF**function.

**=IF(F6-G6<4, F6-G6,4)**

**๐จ Formula Breakdown **

๐ย Firstly,** F6-G6**ย means the deduction of the **General Normal Duty** from the **Duty Hour** performed in a day.

๐ย Secondly, the **IF** function acts based on logic. **F6-G6<4** implies that if the result of** F6-G6** is less than **4** then write the result achieved from** F6-G6**. Else write **4** in the **H6** cell.

- Subsequently, obtain the
**Overtime**of**1 hr**in the**H6**cell. - In addition, use the
**Fill Handle**tool to auto-fill the**H6:H10**range.

- Again,
**Double Time**duty is measured after**12 hours**of duty. Summing up the**General****Duty**and**Overtime**, deduct it from the total**Duty Hour**performed in a day to calculate the**Double Time**duty. - So, input the following formula in the
**I6**cell containing the**SUM**function.

**=F6-(SUM(G6:H6))**

**๐จ Formula Breakdown **

๐ย Firstly, **SUM(G6:H6)** is the sum of **General** **Duty** and **Overtime**.

๐ย Secondly, deduct the sum of** General Duty** and **Overtime** from the total **Duty Hour** performed in a day to get the **Double** **Time** in the **I6** cell.

- Therefore, get the Double time of
**0 hr**in the**I6**cell. Further, use the**Fill Handle**tool to fill the**H6:H10**range automatically. - However, letโs look at the
**I8**cell where Mr. Liam worked on**Wednesday**for**Two hours**of**Double****Time**. Significantly, Mr. Liamโs shift lasted close to**14 hours**.**8 hours**are regarded as**General Duty Time**,**4 hours**are considered o**vertime**, and**2 hours**of**Double Time**.

**๐ Read More: ****5 Ways to Calculate Total Hours Worked in a Week in Excelย ย **

**method**

### 4. Excel Formula for Overtime Over 40 Hours Using IF Function

Mr. Liam performed almost** 54 hours** of duty in the **first week** of **January** whereas **40** is the** General Duty Hour**. Suppose your office limits Overtime to **10 hours** each week and the rest will be added as double time. The use of the **SUM** and **MIN** function will guide us to calculate the overtime and double time. The calculation and method should be as follows. Please check out the steps below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Initially, pick a cell i.e.
**F11**to calculate the sum of**Performed Duty Hours**in a week. - Then, Insert the formula in the
**F11**cell having**SUM**function.

**=SUM(F6:F10)**

- Therefore, obtain the total
**Performed Duty Hour**of almost**54 hours**in the**F11**cell.

- Similarly, calculate the
**General Duties**of**40****hours**. - Next, put the following formula including the
**MIN**function in the**H11**cell.

**=MIN(F11-G11,10)**

- So, the
**MIN**function generally takes the minimum value.**F11-G11**is**13.67**which is greater than**10**so, it writes**10**, the highest no of**Overtime**in a week.

- Now, deduct the sum of
**G11**and**H11**which is the weekly**General Duty**and**Overtime**from the weekly**Performed duty hour****F11**cell. - Then, input the formula containing the
**SUM**function in the**F11**cell.

**=F11-SUM(G11:H11)**

- Finally, we obtain the
**4 hours**of**Double Time**in a week after performing**54 hours**of duty.

**๐ Read More: ****5 Easy Ways to Fix When SUM Time Is Not Working in Excel**

**method**

### 5. Utilize Excel VBA to Create Timesheet with Overtime and Double Time

Using a **VBA code** anyone can create a timesheet with **Overtime** and **Double** **Time**. But applying **VBA** **Macro** is not everyoneโs cup of coffee. During the use of **VBA** **Macro**, we must include various formulas that calculate both **Overtime** as well as **Double** **Time**. But you might have learned that we canโt insert functions and formulas directly while using **VBA**. To learn the ingenious procedure, please check the required steps below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Check first if your
**Developer**option is available or not. - Secondly, to enable the
**Developer**option,**Right-Click**on the mouse at the**Top Ribbon**and select**Customize the Ribbon**.

- Thirdly, click on the
**Developer**option and hit the**OKย**button.

- Now
**Developer**mode is on. - Then, click on the
**Visual Basic**feature**.** - Next, open the dedicated worksheet
**VBA**or the**Module**from the**Insert**menu.

- To begin the procedure, insert the
**VBA code**in the**VBA pop-up**box and hit the**Run**icon to execute.

__Code__

```
Sub Formula_to_Calculate_Overtime_and_Double_Time()
Dim p As Date
Dim q As Date
Dim r As Double
For a = 1 To 5
p = Cells(5 + a, 4).Value
q = Cells(5 + a, 5).Value
Cells(5 + a, 6).Value = (q - p) * 24
r = Cells(5 + a, 6).Value
Cells(5 + a, 7).Value = 8
If (r - Cells(5 + a, 7).Value) < 4 Then
Cells(5 + a, 8).Value = r - Cells(5 + a, 7).Value
Else: Cells(5 + a, 8).Value = 4
End If
Cells(5 + a, 9).Value = r - (Cells(5 + a, 7).Value + Cells(5 + a, 8).Value)
Next a
End Sub
```

- Consequently, get the
**Performed****Duty****Hour**,**General****Duty**,**Overtime**, and**Double****Time**for each day automatically executing a simple**VBA Macro**code.

**๐ Read More: ****7 Examples to Calculate Duration of Time in Excel**

## How to Create an Overtime Payment Calculator in Excel

Mr. Liam now wants to make an Overtime Payment calculator via MS Excel. So, I told him that If he needed to make an overtime payment calculator by inputting **Entry time** and** Exit time**, then **VBA Macro** is the key feature in **MS Excel**. Writing down a **VBA** code, anyone can use the worksheet as a calculator template that calculates the duty time, **Overtime**, and **Total Overtime Bill**. Please follow the necessary procedure below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- In the beginning, to use as an Excel Overtime calculator, Click on the
**Payment**worksheet. - Then,
**Right-Click**on the mouse and get a**Context****Menu**list. - Next, select
**View Code**from the**Context****Menu**list.

- Now, copy or write the following
**VBA****code**in the**VBA****pop-up**box and hit the**Run**icon to proceed.

__Code__

```
Sub Calculate_Payment_for_Overtime()
Dim p As Date
Dim q As Date
Dim WorkHour As Double
Dim d As Integer
Dim r As Double
Dim Rate As Double
p = InputBox(Prompt:="Please insert your Entry Time")
q = InputBox(Prompt:="Please insert your Exit Time")
WorkHour = (q - p) * 24
MsgBox "Your Working Hourย " & WorkHour
d = InputBox(Prompt:="Please insert your General Duty Hour")
r = InputBox(Prompt:="Please insert your Overtime Working Hour Rate")
Rate = (WorkHour - d) * r
MsgBox "Your Overtime Rate:ย $" & Rate
End Sub
```

- First, insert the
**Entry time**and hit the**OK**button.

- Secondly, insert the
**Exit****time**and hit the**OK**button.

- Thus, a pop-up box shows the
**working****hour**of**10****hours**. Further, click on the**OK**button.

- Thirdly, insert the
**General****Duty****Hour**per day and hit the**OK**button.

- Fourthly, insert the
**Overtime****Rate**per hour and hit the**OK**button.

- Finally, the
**Overtime****Bill**for**2 hours**is**$100**in a pop-up box shows up.

## ๐ Important Notes

๐๏ธย Enable the** Developer** menu first to execute** VBA** code.

`๐๏ธ`

ย **Debug** step by step while creating a calculator using **VBA** **Macro**.

๐๏ธย Double Time calculation may change based on company policy.

## ๐ Takeaways from This Article

๐ย **Basic overview** on Overtime and Double time calculation.

๐ย **IF** function to calculate Overtime as well as Double Time.

`๐`

ย **MIN** function to measure Overtime considering the lowest value.

๐ย **SUM** function to sum up the total Duty hour, Overtime, and Double Time.

๐ย **VBA Macro** to calculate Overtime, Double time as well as calculator template.

## Conclusion

In this article, we construe **5 examples** to calculate overtime and double time using formulas briefly in Excel. I hope you enjoyed your learning and will be able to measure the overtime and double time for your company using MS Excel. Any suggestions including queries are appreciated. Donโt hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, donโt forget to visit **www.ExcelDen.com**.

## Related Articles

**4 Easy Ways to Calculate Percentage of Time in Excel****How to Calculate Average Response Time in Excel****2 Ways to Calculate Travel Time Between Two Cities in Excel****Calculate Hours Between Two Dates and Times in Excel Excluding Weekends****6 Approaches to Subtract Time in Excel and Show Negative**