How to Calculate Overtime over 40 Hours Using Excel Formula

Different companies calculate overtime in diverse ways. But the most effective way is calculating overtime when a work week will be over 40 hours. In this way, an employee can work more than 8 hours in an individual day, but his overtime will be calculated when his overall work time will reach over 40 hours.
In this article, you will get the Excel formula for calculating overtime over 40 hours. Also, you may download the Practice Workbook and use it as an Excel Template if you find it necessary for your daily use.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

7 Steps to Calculate Overtime over 40 Hours Using Excel Formula

Generally speaking, Weekly Time Records are used by the human resources department of an organization to keep tabs on how much time an employee spends working during the week. It keeps a record of Regular Time as well as Over Time.

The template (above image) takes four time inputs:

• Time In: This is the time an employee enters the office.
• Lunch Starts: This is the time when lunch starts for the employee.
• Lunch Ends: This is the time when the employee starts his work again after the lunch period.
• Time Out: The time when the employee leaves the office.
• Hourly Rate: You have to input the two hourly rates: Regular and Over Time. In most companies, the Over Time Rate is different from the Regular Rate.

What we get automatically (above image):

• Total Hours: Total Hours an employee worked on a specific day
• Regular Time: This is his regular working hours. An employee can work more than 8 hours in a specific day but Over Time calculation will not start until he has worked over 40 hours for that specific week.
• Over Time: Over Time calculation starts when the employee’s total work exceeds 40 hours in a specific week.
  We also get the Total Pay of the week which includes Total Regular Pay and Total Over Time Pay.

Now, we’ll show the steps involved in creating this template. So, without further delay, let’s dive in!
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

Step 01: Create Basic Outline to Calculate Overtime over 40 Hours Using Excel Formula

Here, we’re showing the full process in different steps so that it gets easy to understand. First of all, we need to create a basic outline of the worksheet. So, just follow along.

• At the very beginning, construct a tempting heading in cell B2. And, apply the Heading 2 cell style in that cell. In this case, we named it Weekly Time Record.
• In cell B4, write down the name of the company.
• Also, in cells in the B6:I12 range, keep spaces to write down the details of the company and of the employee.
• Then, create the outline in cells in the B14:I28 range just like the image below.

Read More: Excel Timesheet Formula with Lunch Break (3 Examples)

Step 02: Enter Required Data to Calculate Overtime over 40 Hours Using Excel Formula

• At this time, we should enter the necessary data like Time In, Lunch Starts, Lunch Ends, and Time Out in the sheet. Also, put the Regular Hourly Rate and Over Time Rate in cells H24 and I24 respectively. Here, we’ve put some sample data into the sheet.

Read More: How to Calculate Hours Worked Minus Lunch with Excel Formula

Step 03: Calculate Daily Working Hours

In this step, we’ll compute the Daily Work Hours worked for each day of the week. So, let’s see it in action.

• To begin with, select cell G16.
• Following this, write down the following formula.

Here, the C16 and F16 cells represent the Time In and Time Out while the D16 and E16 cells refer to the Lunch Starts and Lunch Ends time respectively.

• After that, press ENTER.

• At this moment, bring the cursor to the bottom right corner of cell G16. Thus, it’ll look like a plus (+) sign. It’s the Fill Handle tool.
• Now, double-click on it to use the Fill Handle tool.

• Thus, it exhibits the results in the remaining cells.

Read More: How to Calculate Difference Between Two Dates and Times in Excel

Similar Readings

• How to Calculate the Duration of Time in Excel (7 Methods)
• Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
• How to Subtract Date and Time in Excel (6 Easy Ways)
• Add Minutes to Time in Excel (5 Easy Ways)

Step 04: Determine Overtime over 40 Hours Using Excel Formula

Currently, we’re ready to determine the Over Time of the employee. Let’s explore this step by step.

• Initially, go to cell I16.
• Then, paste the following formula in that cell.

• As usual, tap the ENTER key.

Read More: How to Add Time in Excel Over 24 Hours (4 ways)

Step 05: Calculate Regular Time

To calculate the Regular Time, follow the steps below.

• Firstly, choose cell H16.
• Consequently, enter the formula below.

Here, the MAX function returns the bigger number between these two values: G16-I16 and 0.

For example, in cell H21, our formula is:
=MAX(G21-I21,0)
=MAX(9.17-7.25,0)
=MAX(1.92,0)
= 1.92

• As always, hit ENTER.

Step 06: Compute Total Weekly Hours

Here, we’ll calculate the Total Regular Hours and Total Over Time Hours. Let’s see the process in detail.

• Primarily, select cell H23 and put in the following formula.

Here, H16:H22 represents the cells containing Regular Hours in a week. We used the SUM function to sum up the values in these cells.

• Then, press the ENTER key.

• Thereafter, go to cell I23 and write down the following formula.

Here, I22 represents the cumulative overtime of the week.

• Thus, press ENTER.

Read More: How to Calculate Total Hours in Excel (9 Easy Methods)

Step 07: Compute Total Payment

Finally, we’ll calculate the Total Payment here. So let’s have a look at this procedure.

• At last, select cell H25.
• Then, paste the following formula into that cell.

Here, we’re calculating Regular Pay by multiplying the Total Regular Hours by the Regular Hourly Rate. And the Over Time Pay from the Total Over Time Hours and Over Time Hourly Rate. Then, added these two payments to get the Total Pay of the employee.

• Thus, press ENTER.

Read More: Calculate Hours Between Two Times in Excel (6 Methods)

Conclusion

Your company might calculate Regular Working Hours and Overtime Hours in different ways. For your custom needs, you can modify or extend this template to make a perfect one for your job. Don’t forget to download the Template File. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.

Related Articles

• How to Calculate Time Difference in Excel (13 Ways)
• Timesheet Formula in Excel (5 Examples)
• How to Sum Time in Excel (9 Suitable Methods)
• [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
• How to Calculate Average Response Time in Excel (4 Methods)
• Excel Formula for Overtime over 8 Hours (4 Examples)
• How to Calculate Average Handling Time in Excel (2 Easy Ways)