How to Calculate Hours Worked and Overtime Using Excel Formula (2023)

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

(Video) How to Calculate Overtime Hours on a Time Card in Excel

Step 08: Estimate Total Payment

Bonus Template

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (1)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (2)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (3)

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

(Video) Excel Formula To Calculate Hours Worked And Overtime

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

How to Calculate Hours Worked and Overtime Using Excel Formula (4)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (5)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (6)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (7)

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

How to Calculate Hours Worked and Overtime Using Excel Formula (8)

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

How to Calculate Hours Worked and Overtime Using Excel Formula (9)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (10)

Let me check this formula for the cell I22:

(Video) Microsoft Excel Tutorial - Calculating Overtime Pay for Employees

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

How to Calculate Hours Worked and Overtime Using Excel Formula (11)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (12)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (13)

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

How to Calculate Hours Worked and Overtime Using Excel Formula (14)

(Video) Working Hours & Overtime Formula in Excel | Time Calculations

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (15)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (16)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (17)

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.

How to Calculate Hours Worked and Overtime Using Excel Formula (18)

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 Hours Worked and Overtime Using Excel Formula (19)

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.

(Video) How to Calculate Overtime Working Hours Quickly

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:
  1. Create labeled placeholders. ...
  2. Input the times into their respective cells. ...
  3. Use a formula to calculate hours the employee worked. ...
  4. Determine the employee's pay for that shift. ...
  5. 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?
  1. Step 1: Format your spreadsheet. Open a new Excel file. ...
  2. Step 2: Add timesheet title. Highlight the cell range A1–L1. ...
  3. Step 3: Add required labels. Now it's time to add all the labels to your Excel timesheet. ...
  4. Step 4: Add time-related labels. ...
  5. Step 5: Finishing touches.
3 Sept 2021

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?
  1. Calculate total working hours per month with formulas.
  2. 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 ›

If you're in charge of ensuring employees receive the correct compensation, you may need to track the hours they work. To save time, you can use a program l...
Have a look at how to calculate the number of hours worked in Excel in a few simple steps. The table we prepared contains the time when an employee started a...
The table we prepared contains the time when an employee started and finished work. Hours worked can be calculated easily, as time difference – simply take the ...

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:
  1. In cell B2 type 12:45, and in cell B3 type 15:30.
  2. Type =B2+B3 in cell B4, and then press Enter. ...
  3. To display the time as more than 24 hours, select cell B4.
  4. 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
  1. Step 1: Determine the employee's hourly rate. ...
  2. Step 2: To get the hourly overtime pay, compute for 25% of the hourly rate of a total of 125%. ...
  3. Step 3: Multiply the overtime hourly rate against the hours worked (e.g. 2 hours). ...
  4. Step 4: Add pay with overtime pay for ordinary hours rendered (e.g. 6 hours).
29 May 2022

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.

Videos

1. How to calculate working hours and overtime hours in excel / Use IF formula / Explore
(Explore)
2. Excel 2010 Business Math 44: Payroll Time Sheets, IF Function For Overtime &Gross Pay Calculations
(ExcelIsFun)
3. How to Calculate Hours Worked in Excel
(TEKNISHA)
4. Calculating Overtime in Excel: Daily vs Weekly and more
(Ken Braverman)
5. Calculate Working Hours Excluding Break Time, Overtime & Over Time Pay For The Payroll In Excel BPO
(U Excel in MS office)
6. How to Calculate Hours Worked in Excel
(Excel Tutorials by EasyClick Academy)
Top Articles
Latest Posts
Article information

Author: Jeremiah Abshire

Last Updated: 01/16/2023

Views: 6572

Rating: 4.3 / 5 (54 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Jeremiah Abshire

Birthday: 1993-09-14

Address: Apt. 425 92748 Jannie Centers, Port Nikitaville, VT 82110

Phone: +8096210939894

Job: Lead Healthcare Manager

Hobby: Watching movies, Watching movies, Knapping, LARPing, Coffee roasting, Lacemaking, Gaming

Introduction: My name is Jeremiah Abshire, I am a outstanding, kind, clever, hilarious, curious, hilarious, outstanding person who loves writing and wants to share my knowledge and understanding with you.