How to Calculate Hours Worked and Overtime Using Excel Formula

Most companies calculate work hours and overtime based on a 40 hour workweek (8 hours per day). In this article, we’ll calculate overtime based on the whole week, not based on an individual day. For example, if an employee worked 9 hours on Monday but his total hours for the week do not exceed 40, he will not be compensated for Monday’s overtime. Only when an employee exceeds 40 hours of work for a whole week will overtime compensation be due.

To demonstrate, we’ll create and fill the timesheet below.

We used the Microsoft Excel 365 version in this article, but you may use any other version at your convenience. If any steps don’t work in your version, please leave us a comment and let us know.

Step 1 – Create the Basic Outline

First let’s create a basic outline of the timesheet where we can insert all our necessary inputs and get the desired outputs.

• Construct an enticing heading such as “Calculating Hours Worked and Overtime” in cell B2, and apply the Heading 2 cell style in that cell.
• Leave blank spaces in the B4:I10 range of cells for entry of the employee’s name and the employer’s information.
• Construct some tables in the B12:I26 range of cells as shown in the illustration below.
• Make a place for authorization in the B28:I29 range.

Step 2 – Set Weekly Work Hours and Pay Rate

Now let’s specify the regular and overtime hourly rates, and apply an Excel formula to calculate overtime based on these values.

• In cell D13, enter 40 as the regular hours per week. Any value above 40 will be treated as overtime. If your office maintains different working hours per week, input that value in this field instead.
• Enter the Regular Hourly Rate in cell H13. Here, we used $25/hr.
• Enter the Overtime Hourly Rate, here $37.50/hr. Normally the general working hour rate is lower than the overtime hourly rate.

For the user’s convenience, let’s define names for some cell ranges.

• Change the name of cell D13 to works_hours_per_week.
• Replace the names of cells H13 and I13 with regular_rate and overtime_rate respectively.
• In cells H24 and I24, change the names to work_hrs and overtime respectively.

Note: It’s not mandatory to change the cell name, but doing so helps the end users to understand the internal operation easily.

To define a new name for a cell:

• Select the cell (here, D13).
• In the small box at the top-left side of the display, enter your preferred name.

Step 3 – Enter Required Data

• Enter the necessary data like Time In, Lunch Starts, Lunch Ends, and Time Out in the sheet.

Step 4 – Calculate Daily Working Hours

• In cell G17, enter the following formula:

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 times 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 returns the value in Number format. Otherwise, subtraction of two times results in Time format.

• Press the ENTER key.

• Bring the cursor to the bottom right corner of cell G17 to activate the Fill Handle tool.
• Double-click on it to Autofill the other cells in the range.

The formula is copied to the remaining cells in the G18:G23 range.

Step 5 – Determine Overtime

Before calculating Regular Hours, we calculate the Overtime Hours using the IF function.

• In cell I17 enter the following formula:

where work_hours_per_week represents cell D13.

• Press ENTER.

To check this formula in cell I22, enter:

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

=IF(TRUE,7.25,0)

=7.25

So, the formula returns a value of 7.25.

Step 6 – Compute Regular Time

Now we’ll use the MAX function to compute Regular Time.

• In cell H17 enter the formula below:

This formula returns the maximum of the two values G17-I17 and 0.

• Press ENTER.

Step 7 – Enumerate Total Weekly Hours

With all the base data input, we can now calculate the Total Regular Hours and Total Over Time Hours.

• In cell H24 enter the following formula:

17:H23 represent the cells containing Regular Hours in a week. We used the SUM function to sum up the values in these cells.

• Press ENTER.

• In cell I24 enter the following formula:

Cell I23 holds our Total Overtime hours, because it represents the cumulative overtime for the week.

• Press ENTER.

Step 8 – Estimate Total Payment

In cell H25, we calculate the Total Regular payment due. The formula is the following:

This is simply the multiplication of the Regular Hourly Rate by total Regular Hours.

And we calculate the total Overtime Pay in cell I25. The formula for this is as follows:

This is simply the multiplication of Over Time Rate by Over Time Hours.

Now we can calculate the Grand Total Payment by adding up both the previously calculated payment types.

• In cell H26, enter the following formula:

Bonus Template

This Excel template below can be used directly in your workplace, and will print out on one page (in Letter and A4 format with Landscape orientation) without any modification.

Necessary Inputs:

Put the following inputs in the Excel template:
Overtime After: Enter 40 for 40 hours per week. If your office maintains different working hours, input that value instead.
Hourly Rate: Regular Hourly Rate is usually less than the Overtime Hourly Rate.
Regular: Input the regular hourly rate.
Over Time: Input the overtime hourly rate.
The template takes 4 different time inputs (see the above image):
Time In: The time when the employee enters the working place.
Lunch Starts: The time when lunch starts in the working place.
Lunch Ends: The time when the employee ends lunch.
Time Out: The time when the employee leaves the office.

How to Calculate Overtime for Monthly Salary Employees?

In the previous steps, we used the example of an employee who is paid on a weekly basis. Below is a timesheet for employees who receive salaries on a monthly basis.

Here, we assumed a weekly holiday of one day.

Download Practice Workbook

<< Go Back to Overtime | Formula List | Learn Excel