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

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

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

Good. Send me learning module of Excel in my e-mail I’d: [email protected]

Thanks, Ghosh for your feedback.

What if your company rounds the time? For example: When I clock in at 06:07 it calculates my time starting at 06:00 but if I were to clock in at 06:08, my start time is calculated at 06:15. By the way, Your emails are great. I highly enjoy them.

Hi BRIAN WINKLE,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. To create a timestamp like the given example, you can take a look at the article below.

https://www.exceldemy.com/create-a-timesheet-in-excel/

I hope this will help you to solve your problem.

Thanks!

I will check this issue and let you know. Glad to know that we can add some value via our emails.

Best regards

Kawser

These formulas work pretty good if you work the 1st or 2nd shift. I work from 11:00pm – 7:30am with a 1/2 lunch period. When I enter these times, the formulas do not work. Any way to update this to work with these start and stop times?

Hi JIM,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. You need to apply the formula below in Cell G16:

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

I hope this will help you to solve your problem. Please let us know if you have other queries.

Thanks!

Hi sir

Please send me practice data for advanced Excel so I can practice.

Thanks

Mdu

You will also find the file in our article. The file is at the upper part of the article. You will find the file under this title: “Calculate Hours Worked and Overtime Excel Template”.

Best regards

Kawser Ahmed

In the overtime column, it is calculating the total overtime for the week. What if you wanted to just calculate the overtime hours for that day. For example in cell I22, instead of reading the total for the week (12.25) it just read the overtime for that day (5.0).

Thank You!!

Hi JOE FRAZIER,

Thanks for your comment. I am replying to you on behalf of Exceldemy. In this article,

Cells I16:I22ofColumn Icounts the overtime for each day of a week. That means if you want to see the overtime forMonday, you need to checkCell I16. So, you can use the same formula to calculate overtime for a single day.I hope this will help you to solve your problem. Please let us know if you have other queries.

Thanks!

This was exactly what I was looking for! Is there a way for it to be multiple employees, in a table format verse just an individual log? Playing around with the formula now, seeing how I can enter more than one employee and the formula continues correctly

Thanks, Christina for your feedback. Glad to know that it helped you someway.

Kawser,

Please help me…I’m stuck on step 2. The overtime formula refers to “work_hours_per_week”, but I don’t see that specific name anywhere on the spreadsheet. Is that written somewhere for it to pull from? I’m trying to create a spreadsheet of my own following the format, but how can I get it to pull from a cell without a name?

Hi DEE ZELAYA,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. Here,

Cell D12is renamed aswork_hours_per_week. You can use theName Managerin theFormulastab to define the name. To check the defined names, follow the steps below.Firstly, download the practice book, go to the

Formulastab and selectName Manager.In the

Name Managerbox, you will find all the defined names.To apply the same formula in your new spreadsheet, you need to define the names using the

Name Manager. To do that, you can follow the link below.https://www.exceldemy.com/how-to-edit-named-range-in-excel/

Thanks!

Maybe a bit late for a reply, but I only came across this today.

The cell where it says 40, is renamed as work_hours_per_week

Top left next to the function bar you can rename a cell you choose

Please help me…I’m stuck on step 2. Name appears in the Colum or sum

Hi FEITY LAU,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. I guess you are facing the problem because of not defining the names. Here,

Cell D12is renamed aswork_hours_per_week. You can use theName Managerin theFormulastab to define the name. To check the defined names, follow the steps below.Firstly, download the practice book, go to the

Formulastab and selectName Manager.In the

Name Managerbox, you will find all the defined names.To apply the same formula in your new spreadsheet, you need to define the names using the

Name Manager. To do that, you can follow the link below.https://www.exceldemy.com/how-to-edit-named-range-in-excel/

Thanks!

When I do this my mins are always off. example:

Shift start (E6)

0756

lunch start (F6)

1325

lunch end (G6)

1354

shift end (H6)

1635

My read back is 8 hr and 17 min. However, it should be 8 hr and 10 min.

I used the above formula:

=((H6-E6)-(G6-F6))*24

Am I missing something?

Also, the return time for G16 above appears to me to be wrong. After I converted it to military time the return time should have been 8.20 or 8 hours and 20 min. and shown is 8.33 or 8 hrs and 33 min.

Hi CHRISTIAN,

Thanks for your comment. I am replying to you on behalf of Exceldemy. The time difference in the above article is in

Numberformat. That is why 8.17 doesn’t mean 8 hours 17 min. It actually means 8 hours and 10 minutes. To get the results in the desired format, you can follow the steps below.First of all, select

Cell G16in the dataset and type the formula below:`=((F16-C16)-(E16-D16))`

Hit Enter to see the result.

Select Cell G16 again, go to the

Hometab, and click on theNumber Formaticon. It will open theFormat Cellswindow.In the Format Cells window, click on the

Numbertab and selectTime.Then, select 37:30:55 from the Type box.

As a result, you will see the result in the desired format.

I hope this will solve your problem. Please let us know if you have any other queries.

Thanks!