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-hour workweek (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.
How to Calculate Hours Worked and Overtime Using Excel Formula: 8 Steps
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. We will apply the Excel formula to calculate overtime over 40 hours. 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 calculated time worked with an Excel formula 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.
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.
Step 05: Determine Overtime
However, the template shows the Regular Hours before the Overtime Hours. Before Regular Hours, I actually calculated the Overtime Hours using the Excel IF function formula.
- Initially, go to cell I17 and write down the following formula.
Where work_hours_per_week represents cell D13.
- 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(47.25>40,47.25-40,0) [I have replaced some parts of the formula with their values.]
So, the formula returns a value of 7.25.
Read More: Excel Formula for Overtime over 8 Hours
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.
This formula returns the maximum one between these two values: G17-I17 and 0.
- As always, press ENTER.
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.
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.
Cell I23 actually holds our total Overtime 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.
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.
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.
It indicates the sum of Regular Pay and Over Time Pay.
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.
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 get 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.
You may download the following Excel workbook with a free template for better understanding and practicing yourself.
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 do understand how the formulas work, you can modify and extend this template to fulfill your specific criteria. So, these are the glimpses 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.