Excel formula makes any calculation very easy. That’s why Excel is widely used in workplaces. Most companies calculate work hours and overtime based on the 40-hours-work-week (8 hours per day). In this article, we’ll calculate overtime based on the whole week, not based on an individual day. Here, we are going to discuss the **Excel formula to calculate hours worked and overtime** in detail with proper illustrations.

**Table of Contents** hide

Download Practice Workbook

8 Steps to Calculate Hours Worked and Overtime Using Excel Formula

Step 01: Create the Basic Outline

Step 02: Set Weekly Work Hours and Pay Rate

Step 03: Enter Required Data

Step 04: Calculate Daily Working Hours

Step 05: Determine Overtime

Step 06: Compute Regular Time

Step 07: Enumerate Total Weekly Hours

Step 08: Estimate Total Payment

Bonus Template

How to Calculate Overtime for Monthly Salary Employees

Conclusion

Related Articles

## Download Practice Workbook

You may download the following Excel workbook with a free template for better understanding and practicing yourself.

Calculating Hours Worked and Overtime.xlsx

## 8 Steps to Calculate Hours Worked and Overtime Using Excel Formula

It is easy to build an Excel formula to calculate the hours worked and overtime of your employees. Before starting the main work, let’s just explain our overtime criteria. Here, we’re computing overtime based on the working hours of a whole week. We’re not considering the work time of a single day to calculate the overtime. Suppose, an employee has worked 9 hours on the very first day of the week. But if his total hours in the week do not exceed 40 hours, he will not be rewarded for any overtime. Whenever an employee crosses 40 hours of work in a week, his overtime will start counting.

Now, we’ll show the steps involved in creating this timesheet. So, without further delay, let’s dive in!

Here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience. If any steps won’t work in your version, then leave us a comment.

__Step 01__: Create the Basic Outline

At the very beginning, we should create a basic outline of the sheet where we can insert all our necessary inputs and get the desired outputs. Let’s explore the steps in detail.

- First of all, construct an enticing heading in cell
**B2**. And apply the**Heading 2**cell style in that cell. In this case, we named it**Calculating Hours Worked and Overtime**. - Additionally, leave blank spaces in the
**B4:I10**range of cells so that you can write down the name of the employee and the employer’s information. - Next, construct some tables in the
**B12:I26**range of cells as shown in the illustration below. - At last, make a place for authorization in cells in the
**B28:I29**range.

__Step 02__: Set Weekly Work Hours and Pay Rate

In this section, we’ll set the working hours per week after which overtime counting will start. Also, we will specify the regular and overtime hourly rate. It’s quite simple. Just follow along.

- In cell
**D13**, write down**40**as the regular hours per week. That means if the employee works for 40 consecutive hours, after that, every hour will be treated as overtime. If your office maintains**45**or other working hours per week, input that value in this field. Working hours vary from country to country. - After that, input the
**Regular Hourly Rate**in cell**H13**. Here, we put it as**$25/hr**. - Next, enter the
**Overtime Hourly Rate**. In this case, we take it as**$37.50/hr**. Normally the general working hour rate is different from the overtime hourly rate.

For the user’s convenience, we defined names for some cell ranges.

- First, change the name of cell
**D13**to**works_hours_per_week**. - Secondly, replace the names of cells
**H13**and**I13**as**regular_rate**and**overtime_rate**. - Then, in cells
**H24**and**I24**, convert the cell name to**work_hrs**and**overtime**.

*Note:** It’s not mandatory to change the cell name. But helps the end users to catch the internal operation easily*.

Here comes the question. How can we define a new name for a cell? Don’t worry. We’re here for you.

- Firstly, select the cell. Accordingly, go to cell
**D13**. - Then, proceed to the small box at the top-left side of the display and give your preferred name.

__Step 03__: Enter Required Data

- At this time, we should enter the necessary data like
**Time In**,**Lunch Starts**,**Lunch Ends**, and**Time Out**in the sheet. Here, we’ve entered some sample data into the sheet.

__Step 04__: Calculate Daily Working Hours

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

- To begin with, select cell
**G17**. - Following this, write down the following formula.

`=((F17-C17)-(E17-D17))*24`

Here, the **C17** and **F17** cells represent the **Time In** and **Time Out** while the **D17** and **E17** cells refer to the **Lunch Starts** and **Lunch Ends** time respectively. **(F17-C17)** is actually **(Time Out – Time In)**. And **(E17-D17)** is **(Lunch Ends – Lunch Starts)**. We multiplied **((Time Out – Time In) – (Lunch Ends – Lunch Starts))** by **24** to convert it into an hour value. This is why we get the value in **Number** format. Otherwise, subtraction of two times results in **Time** format.

- Then, press the
**ENTER**key.

- Currently, bring the cursor to the bottom right corner of cell
**G17**. 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.

As a result, it displays the results in the remaining cells in the **G18:G23** range.

**Read More: **Excel Timesheet Formula with Lunch Break and Overtime

__Step 05__: Determine Overtime

Though the template shows the **Regular Hours** before the **Overtime Hours**, I actually calculated the *Overtime Hours* before the *Regular Hours*.

- Initially, go to cell
**I17**and write down the following formula.

`=IF(SUM($G$17:G17)>work_hours_per_week,SUM($G$17:G17)-work_hours_per_week,0)`

Where **work_hours_per_week** represents cell **D13**.

**Formula Breakdown**

- This formula has an expanding range. It is
**$G$17:G17**. For the next cell (**I18**) in the column, this range will be**$G$17:G18**. You see the range has expanded. This is why it is called expanding range. **logical_test:****IF function**checks whether the sum of the expanding range has exceeded the value of**work_hours_per_week**.**value_if_true:**If the sum exceeds the value, the**IF function**returns this value:**SUM($G$17:G17)-work_hours_per_week**.**value_if_false:**Otherwise, the**IF function**returns a value of**0**.

- Formerly, hit
**ENTER**.

Let me check this formula for the cell **I22**:

`=IF(SUM($G$17:G22)>work_hours_per_week,SUM($G$17:G22)-work_hours_per_week,0)`

**=IF(47.25>40,47.25-40,0)** [I have replaced some parts of the formula with their values.]

**=IF(TRUE,7.25,0)**

**=7.25**

So, the formula returns a value of **7.25**.

**Read More:** Excel Formula to Calculate Overtime and Double Time (3 Ways)

__Step 06__: Compute Regular Time

Now, we’re considering computing the **Regular Time**. We’ll use the MAX function here. To do this, follow the steps below.

- Primarily, select cell
**H17**and put the formula below.

`=MAX(G17-I17,0)`

This formula returns the maximum one between these two values: **G17-I17** and **0**.

- As always, press
**ENTER**.

**Read More:** Excel Formula for Overtime over 8 Hours (4 Examples)

__Step 07__: Enumerate 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
**H24**and put in the following formula.

`=SUM(H17:H23)`

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

- Secondarily, press
**ENTER**.

- Again, go to cell
**I24**and enter the following formula.

`=$I$23`

Cell **I23** actually holds our total **Over Time** hours. Because it represents the cumulative overtime of the week.

- As usual, tap
**ENTER**.

__Step 08__: Estimate Total Payment

In cell **H25**, we got the total regular pay. The formula is the following.

`=regular_rate*work_hrs`

It’s actually the multiplication of the **Regular Hourly Rate** and total **Regular Hours**.

Also, we got the total overtime pay in cell **I25**. The formula associated with this is below.

`=overtime_rate*overtime`

Actually, it’s the multiplication of the **Over Time Rate** and total **Over Time Hours**.

Then, we achieved the grand Total Payment by adding up the previously calculated two types of payment.

- In cell
**H26**, write down the following formula.

`=$H$25+$I$25`

It indicates the sum of **Regular Pay** and **Over Time Pay**.

## Bonus Template

This Excel template can be directly used in your workplace. Below is the template overview. We can print this template on one page (perfect for **Letter** and **A4** Page with Landscape orientation) without any modification.

**Necessary Inputs:**

Put the following inputs in the Excel template:**Overtime After:** Generally, this is 40 hours per week. If your office maintains 45 or other working hours, input that value in this field.**Hourly Rate: **Normally, **Regular Hourly Rate** is different from the **Over Time Hourly Rate**.**Regular:** Input the regular hourly rate.**Over Time:** Input the overtime hourly rate.

The template takes **4** different time inputs (above image):**Time In:** This is the time when the employee enters the working place.**Lunch Starts:** Time when lunch starts in the working place.**Lunch Ends:** It’s the time when the employee ends lunch.**Time Out:** This is when the employee completely leaves the office.

That’s how you can easily use this template.

## How to Calculate Overtime for Monthly Salary Employees

In our previous steps, we basically talked about an employee who is paid on a weekly basis. But what about the employees who got salaries on a monthly basis? Don’t get tense. Follow **this article** to address that issue. Follow the image below to get a better idea.

Here, we assumed a weekly holiday of one day. It’s also a ready-to-use template that will be very useful for you.

## Conclusion

Whatever hourly rate your company provides and whatever working hours per week your company has adopted, this template will suit your company. Even if you did understand how the formulas work, you can modify and extend this template to fulfill your specific criteria. So, these are the glimps of the Excel formula I use to calculate hours worked and overtime in a week. 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 Overtime Percentage in Excel (3 Quick Methods)

## FAQs

### What is the formula for calculating overtime in Excel? ›

Enter "=IF(A1-40>0,(A1-40)*A3,0)" in cell A5. This calculates the amount of overtime pay, if any. In the example, this would calculate 8 hours of overtime pay.

**How do I calculate actual hours worked in Excel? ›**

**Follow the steps below to calculate hours worked in Excel:**

- Create labeled placeholders. ...
- Input the times into their respective cells. ...
- Use a formula to calculate hours the employee worked. ...
- Determine the employee's pay for that shift. ...
- Calculate the total weekly hours and pay.

**How do you calculate hours worked overtime? ›**

Overtime pay is calculated: **Hourly pay rate x 1.5 x overtime hours worked**. Here is an example of total pay for an employee who worked 42 hours in a workweek: Regular pay rate x 40 hours = Regular pay, plus. Regular pay rate x 1.5 x 2 hours = Overtime pay, equals.

**What is the formula to calculate working hours? ›**

Just **multiply the hours worked per day by the number of days worked per week**. If an employee works different schedules every day, then you will need to calculate hours worked for each day. You will then need to add up the total for each day to get their hours worked in a given week.

**How do I create a timesheet in Excel with overtime? ›**

**How To Create a Timesheet in Excel?**

- Step 1: Format your spreadsheet. Open a new Excel file. ...
- Step 2: Add timesheet title. Highlight the cell range A1–L1. ...
- Step 3: Add required labels. Now it's time to add all the labels to your Excel timesheet. ...
- Step 4: Add time-related labels. ...
- Step 5: Finishing touches.

**How do I calculate hours and days in Excel? ›**

In the Formula Type drop down list, please select Date option. Then, select Count days, hours and minutes between dates option in the Choose a formula list box; In the right Arguments input box, select the cells which contain the start date and end date that you want to use to calculate.

**How do employers calculate hours worked? ›**

All you need to do is **divide your minutes by 60**. For example, say your employee worked 20 hours and 15 minutes during the week. Divide your total minutes by 60 to get your decimal. For this pay period, your employee worked 20.25 hours.

**How do you calculate total hours worked in a month in Excel? ›**

**How to calculate working hours per month in Excel?**

- Calculate total working hours per month with formulas.
- Enter this formula: =NETWORKDAYS(A2,B2) * 8 into a blank cell where you want to put the result, and then press Enter key, and you will get a date format cell as following screenshot shown:

**› ... › Career development ›**

### How To Calculate Hours Worked in Excel (With Tips)

### How to Calculate Hours Worked in Excel

### How to Calculate Hours Worked in Excel

**What is OT formula? ›**

In most cases, overtime pay rate is **1.5 times the employee's regular rate of pay**, which is commonly referred to as “time and a half”.

### How do you calculate overtime in numbers? ›

The standard overtime rate is **1.5 times the employee's regular hourly wage**. This number is also commonly known as “time-and-a-half.” So if one employee makes $15 per hour, their overtime rate is $22.50 per hour ($15 x 1.5). If another employee makes $25 per hour, their overtime rate is $37.50 per hour ($25 x 1.5).

**How do you calculate 1.5 OT? ›**

**1.5x or not less than one and a half times the hourly rate of pay**. 2. During rest days: Less than half of normal working hours.

**How do you calculate overtime pay? ›**

Using the time-and-a-half rule of thumb, you'd calculate overtime pay by **multiplying an employee's hourly rate by 1.5 and then multiplying the result by the number of overtime hours they worked**. You then add that total to the amount they make within a 40-hour workweek.

**How do I calculate late time in Excel? ›**

You can use that fact to perform both of your tasks, calculating your late time as **(A2-ROUNDDOWN(A2;0))-B2**, ie (date and time minus date) minus time. If your that number is positive that are your hours, minutes and seconds late.

**How do I calculate time in Excel in 24 hours? ›**

**To add up more than 24 hours:**

- In cell B2 type 12:45, and in cell B3 type 15:30.
- Type =B2+B3 in cell B4, and then press Enter. ...
- To display the time as more than 24 hours, select cell B4.
- On the Home tab, in the Cells group, choose Format, and then choose Format Cells.

**What is OT in timesheet? ›**

OT (**time and a half**) is paid only after 40 worked hours, or 32 worked hours in the case of a Stat holiday. 2.) DT (double time) is only paid for work on Sundays if the employee has worked 40 regular hours (or 32 worked hours in the case of a Stat holiday), and 8 hours on Saturday.

**How are pay and hours calculated? ›**

First, **determine the total number of hours worked by multiplying the hours per week by the number of weeks in a year (52).** **Next, divide this number from the annual salary**. For example, if an employee has a salary of $50,000 and works 40 hours per week, the hourly rate is $50,000/2,080 (40 x 52) = $24.04.

**How much is overtime for 19 an hour? ›**

What's time and a half of 19.00? You make $19.00 normally. Time and a half is **$28.50 an hour**.

**What is overtime for $28 an hour? ›**

You make $28.00 normally. **Time and a half** is $42.00 an hour.

**How do you calculate overtime on 2022? ›**

**3.**

**Computation**

- Step 1: Determine the employee's hourly rate. ...
- Step 2: To get the hourly overtime pay, compute for 25% of the hourly rate of a total of 125%. ...
- Step 3: Multiply the overtime hourly rate against the hours worked (e.g. 2 hours). ...
- Step 4: Add pay with overtime pay for ordinary hours rendered (e.g. 6 hours).

### How do you calculate overtime hours per month? ›

**Count the total number of hours for every other workweek in the pay period**. Any employee who has worked more than 40 hours in any seven-day workweek is owed overtime at a rate of 1.5 times his or her regular rate of pay, according to federal law.