How to Calculate Overtime and Double Time with Formula in Excel

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.

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.

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

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,

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.

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

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

• 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

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.

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.

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

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

• 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

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.

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.

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

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

• 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 overtime, and 2 hours of Double Time.

📕 Read More: 5 Ways to Calculate Total Hours Worked in a Week in Excel  

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.

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

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

• 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

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