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.

**Table of Contents**hide

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

`=((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.

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

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

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

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

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

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

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

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.