How to Calculate Hours Worked and Overtime Using Excel Formula

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.


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.

excel formula to calculate hours worked and overtime

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.

Create the Basic Outline


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.

Set Weekly Work Hours and Pay 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.

Set Weekly Work Hours and Pay Rate to Calculate Hours Worked and Overtime


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.

Enter Required Data


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.

Calculate Daily Working Hours

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

Calculate Daily Working Hours Using Excel Formula

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.

Determine Overtime

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.

Determine Overtime Using Excel Formula

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.

Compute Regular Time

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.

Enumerate Total Weekly Hours

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

Estimate Total Payment

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.

Estimate Total Payment of Hours Worked and Overtime Using Formula


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.

Bonus Template

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.

How to Calculate Overtime for Monthly Salary Employees Using Excel Formula

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

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

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

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

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

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

  5. 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:I22 of Column I counts the overtime for each day of a week. That means if you want to see the overtime for Monday, you need to check Cell 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!

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

  7. 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 D12 is renamed as work_hours_per_week. You can use the Name Manager in the Formulas tab to define the name. To check the defined names, follow the steps below.
      Firstly, download the practice book, go to the Formulas tab and select Name Manager.

      In the Name Manager box, 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

  8. 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 D12 is renamed as work_hours_per_week. You can use the Name Manager in the Formulas tab to define the name. To check the defined names, follow the steps below.
      Firstly, download the practice book, go to the Formulas tab and select Name Manager.

      In the Name Manager box, 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!

  9. 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 Number format. 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 G16 in the dataset and type the formula below:
      =((F16-C16)-(E16-D16))
      Hit Enter to see the result.

      Select Cell G16 again, go to the Home tab, and click on the Number Format icon. It will open the Format Cells window.

      In the Format Cells window, click on the Number tab and select Time.
      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!

Leave a reply

ExcelDemy
Logo