Md. Ahaduzzaman
Almost in every office environment nowadays we have to keep the working hours record of the employees. It is necessary when we want to know how many hours they have worked so that we can prepare the salary for the individual employees. Working overtime means that a particular employee should get extra payments for the extra hours. So it is quite important for us to keep track of the overtime of the employees. In this article, we will discuss 4 simple ways to prepare an Excel formula for overtime over than 8 hours. We will use Excel functions such as TIME, TIME & IF, MIN, and MAX. You can take a glance at the overview snapshot below to know the Methods and Outcomes of this article.
📁 Download Excel File
Download the practice file from here.
Definition of Overtime in Work?
When an employee works longer than his regularly allocated working hours, it is referred to as Overtime in work. The generic meaning of Overtime simply refers to hours an employee does outside of the usual workday. We also frequently use Overtime to describe the pay for such work. So if an employee works more than the usual hours his/her extra time, as well as the extra payment, can be characterized as Overtime.
Standard Working Hours And Overtime Calculation Formula
In every company, there is a specific time frame when the employees work. This time frame is the Standard working hours. It may vary from company to company but the basic idea holds everywhere. If such a situation arises when the employees need to work more than their usual hours then they are working Overtime. So the Overtime calculation formula is as follows.
Overtime= Extra working hours-Regular working hours
So we basically deduct the Regular working hours from the Extra working hours to get the Overtime.
Learn to Create Excel Formula for Overtime Over 8 Hours with These 4 Methods
In this article, we will discuss 4 ways to prepare an Excel formula for overtime over 8 hours. Let’s assume that we have a dataset of the Employee Work Record of a company. We know the Entry Time & Exit Time of each individual person. At this moment we are trying to keep the Overtime record so that we can use that to prepare the salary sheet at the end of the month. In the following sections, we will discuss the methods briefly. The sample dataset to prepare an Excel formula for overtime over 8 hours is as follows.
1. Applying TIME Function
In this section, we will apply TIME function for creating an Excel formula for overtime over 8 hours.
This function gives us the decimal number for a specific time. The syntax of this function is TIME(hour, minute, second). So if we insert the hour, minute, and second as arguments this function will return the particular time. We can use this function if we want to get a specific time as a return value. So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell F6 formula box and write this formula.
=E6-TIME(8,0,0)
- Here TIME(8,0,0) returns the time as 8:00.
- Following that we use the Fill Handle tool to drag the formula.
- Finally, we get our result in column F.
📕 Read More: How to Calculate Turnaround Time in Excel Excluding Weekends
2. Applying TIME & IF Functions to Apply Conditions
In this section, we will discuss how we can use the TIME & IF functions together to create an Excel formula for overtime over than 8 hours. The syntax for the IF function is IF(logical_test, [value_if_true], [value_if_false]). So this function is useful when we want to do a specific task depending on some logical test. Now the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell F6 formula box and write this formula.
=IF(E6-TIME(8,0,0)>=TIME(1,0,0),E6-TIME(8,0,0),0)
🔨 Formula Breakdown
👉 E6-TIME(8,0,0)>=TIME(1,0,0) here are the criteria that we want to check.
👉 If the result is true then the return will be E6-TIME(8,0,0).
👉 Otherwise the return will be 0.
- Now we use the Fill Handle tool to drag the formula.
- Finally, we get the result in column F.
📕 Read More: 7 Examples to Calculate Duration of Time in Excel
3. Applying MIN Function
In this section, we will discuss how we can use the MIN function to create an Excel formula for overtime over than 8 hours. The syntax for the MIN function is MIN(number1, [number2], …). This function returns us the minimum value from a given data range. So if we want to know the lowest value from a set of values we can use this function. So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell F6 formula box and type our formula.
=MIN(8,E6)
- This function returns the minimum value among the 2 values.
- Now we write this formula in cell G6 to get the Overtime.
=E6-F6
- After that, we apply the Fill Handle tool to copy the formula in columns F & G.
- Finally, we will get our result in column G.
📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
4. Using MAX Function
In our final section here we will use the MAX function to create an Excel formula for overtime over than 8 hours. The syntax for the MAX function is MAX(number1, [number2], …). This function returns us the highest value among 2 or more numbers. So it is very handy when we want to know the highest value in a data range. So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell G6 formula box and type this formula.
=MAX(0,E6-F6)
- This will return the highest value among the 2 numbers.
- Now we employ the Fill Handle tool for dragging the formula.
- Finally, we get our result in column G.
📕 Read More: 5 Ways to Calculate Total Hours Worked in a Week in Excel
How to Calculate Overtime Excluding Lunch Time in Excel
In this section, we will apply the MOD function to calculate the overtime excluding lunch Time in Excel. The syntax for this function is MOD(number, divisor). This function returns us the remainder after dividing by the divisor. So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell G6 formula box and put this formula.
=(MOD(F6-C6,1)-MOD(E6-D6,1))-TIME(8,0,0)
- Here (MOD(F6-C6,1)-MOD(E6-D6,1)) returns us the net total work time.
- We deduct the regular work time given by TIME(8,0,0) to get the overtime.
- Following that we apply the Fill Handle tool to drag the formula.
- Finally, we get our result in column G.
📕 Read More: 4 Examples of Timesheet Formula with Lunch Break in Excel
Common Mistakes to Avoid in Overtime Calculation
When we are trying to properly calculate the overtime of the employees we have to be careful about some usual mistakes that we can make. Here we will discuss some common mistakes that we need to be wary of.
- The number of non-discretionary bonuses, shift differentials, and some other forms of remuneration is all part of an employee’s regular rate of pay in addition to their hourly wage. As a result, if the employee receives these additional forms of payment, we must take them into account when calculating their normal rate of pay.
- We must not assume that incentives are optional so that we can deduct them from the standard rate of pay.
- We must prorate a non-discretionary bonus over the whole bonus period when an employee earns it over more than one workweek.
- We should not forget to take into account different hourly rates.
- We should not misuse the variable workweek approach.
- We need to estimate the regular rate of pay for tipped workers properly.
📄 Important Notes
🖊️ When we use Excel functions we need to be careful about the function arguments
🖊️ We have to use the Custom data type as h:mm to properly calculate our result.
📝 Takeaways from This Article
📌 We can use TIME function for preparing an Excel formula for overtime over than 8 hours.
📌 We can also use both the TIME & IF functions together to prepare an Excel formula for overtime over than 8 hours.
📌 MIN function is another way to do this task.
📌 Also we can employ the MAX function to prepare an Excel formula for overtime over than 8 hours.
Conclusion
In this article, we have learned 4 different ways to prepare an Excel formula for overtime over than 8 hours. We can use the TIME, TIME & IF, MIN, and MAX functions to do this task. Users can use any of the methods they like and feel comfortable with. If you want to ask any questions about this article feel free to comment in our comment box. The writer will try to answer them. But if you are looking for more Excel-related problems and solutions you can always visit our website www.Excelden.com.
Related Articles
- 4 Easy Ways to Calculate Percentage of Time in Excel
- How to Calculate Average Response Time in Excel
- 9 Tricks to Calculate Time Difference Between AM and PM in Excel
- 2 Ways to Calculate Travel Time Between Two Cities in Excel
