Buzz
To calculate overtime hours in Excel, we need to use a combination of various Excel functions, fortunately, most of which are basic functions in Excel. If you understand the basic functions in Excel, simply follow the instructions below from Mytour to quickly grasp it.
Guide to Calculating Overtime Hours in Excel
In the example provided in this article, we will take data from 3 employees working overtime within a week, with the clock-in time set at 8:00 AM and the clock-out time at 5:00 PM. In the guide on calculating overtime hours in Excel, we will calculate overtime in minutes and display it by each day.
- The company's working hours are from 7:00 AM to 5:00 PM, meaning if it's past 5:00 PM, it will be considered overtime.
- Note that the time in the article is set to the standard display format of Vietnam.
Step 1: First, to calculate overtime hours in Excel, we will need to use several functions which we will list below:
- IF Function: Conditional function.
- TIME Function: Function that uses time values, including HOUR.
- ROUND Function: This function rounds values according to how you set it.
=> To calculate overtime hours in Excel, enter the following command into the first cell of column G:
=IF(HOUR(F5)>=17,ROUND((F5-TIME(17,0,0))*24*60,0),0)
Explanation:
->HOUR(F5)>=17, sets the value of Column F at position 5 (F5) with the HOUR function >=17, meaning if the clock-out time is greater than or equal to 17, it will be considered overtime.
->ROUND((F5-TIME(17,0,0))), Using the Round function externally to round off, internally we apply the formula of subtracting time column F5 by 17h:0:0 to yield additional working hours.
-> Then multiply by 24 to display hours, multiply again by 60 to display minutes as per the task requirements. If you want to display seconds, just multiply by 60 more.
->Comma 0 next is for rounding off value, meaning there won't be any odd seconds, only even minutes. And finally, the last 0 value is the False value of the IF function in case the value in the array doesn't meet the condition here of being greater than 17 hours.
Step 2: Note in case you type a formula and encounter the There's problem with this Formula error, it's not because you entered the wrong formula, although not every Excel machine encounters it, it's simply a formatting error between dot and comma.
Specifically, we'll change all commas(,) to semicolons(;) to make the formula work again.
Step 3: Immediately you'll see the result showing extra minutes as 80 minutes have been rounded off. You can subtract 17 hours to double-check if the extra working time calculation in Excel is correct or not.
Applying the formula to all remaining rows, we'll have the method of calculating extra working time in Excel applied for each day. If you want to calculate the total, you can use the SUM function.
Simply understanding the 3 functions IF, Time, and Round allows you to apply them to calculate extra working time in Excel. Not only with Excel's method of calculating extra working time but in the following Mytour articles, you'll learn more about many other Excel functions. In case Excel misinterprets dates, refer to how to fix date errors in Excel here.
Especially, basic Excel functions in accounting are always essential in the workplace. Mastering Excel functions in accounting will be very beneficial for those who are new to the job.
