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

**Table of Contents**Expand

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

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

__Step 03__: Calculate Daily Working Hours

In this step, weâ€™ll calculate Daily Work Hours including Overtime with Excel formula 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.

`=((F16-C16)-(E16-D16))*24`

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.

**Formula Breakdown**

Generic formula: **((Time Out â€“ Time In) â€“ (Lunch Ends â€“ Lunch Starts)) x 24
**How does this formula work?

**(F16-C16):**This part gives us the total time an employee has been in the office. For example, (5:00 PM â€“ 8:00 AM) = 0.375- From this time
**(Time Out â€“ Time In)**, we deduct the*Lunch Time***(E16-D16)**. So, 0.375 â€“ (11:40 AM â€“ 11:00 AM) = 0.375 â€“ 0.0278 = 0.347222 - Then we multiply the whole result by 24 to make it a time value. 0.347222 x 24 = 8.33 Hours.

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

__Step 04__: Determine Overtime over 40 Hours Using Excel Formula

Currently, weâ€™re ready to apply Excel **IF** function to calculate the Overtime in hours of the employee. Letâ€™s explore this step by step.

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

`=IF(SUM($G$16:G16)>40,SUM($G$16:G16)-40,0)`

**Formula Breakdown**

Syntax of **IF Function**: **IF(logical_test, value_if_true, [value_if_false]**

**SUM($G$14:G14)>40:**This is theargument of the*logical_test***IF function**. This part checks whether the sum of the cell range**$G$16:G16**is greater than*40*or not. This cell range will extend when we shall copy the formula for other cells in the column.**SUM($G$14:G14)-40:**It is theargument. It returns the subtraction of*value_if_true***SUM($G$14:G14)**and**40**.

- As usual, tap the
**ENTER**key.

__Step 05__: Calculate Regular Time

To calculate the *Regular Time*, follow the steps below.

- Firstly, choose cell
**H16**. - Consequently, enter the formula below.

`=MAX(G16-I16,0)`

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.

`=SUM(H16:H22)`

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.

`=I22`

Here, I22 represents the cumulative overtime of the week.

- Thus, press
**ENTER**.

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

`=H23*H24+I23*I24`

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

**Download Practice Workbook**

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

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

**<< Go Back to Overtime | Formula List | Learn Excel**

This setup is the best I have found yet, however, I can’t seem to get the overtime column to function quite right. Once overtime is reached, it carries that amount down through the rest of that week’s overtime rows. For example, my week is Sunday-Saturday, if I hit 40 hours by Thursday(say I have 2.5 hrs overtime on Thursday), it places 2.5 hours in overtime for Friday and Saturday as well. That brings my total overtime to 7.5 for the week when it should only be 2.5. What am I missing?

I was having trouble with that as well. My solution was to add another calculation in the formula to subtract all previous overtime from the week

The formula for Monday overtime would stay the same:

=IF(SUM($G$14:G14)>40,SUM($G$14:G14)-40,0)

Then for Tuesday-Sunday the formula would subtract previous overtime after subtracting the 40 hours

=IF(SUM($G$14:G14)>40,SUM($G$14:G14)-40-sum($I$14:I14),0)

and to calculate all overtime in I21:

=sum(I14:I20)

Hi Kaitlin,

Can you please share your Excel sheet with me? Here is the email: [email protected]. Thanks.