Calculating hours worked - Excel Tips and Tricks
Вставка
- Опубліковано 21 жов 2024
- Here is a quick formula on how to calculate hours worked.
Use this formula.
=MOD(RIGHT(A2,5)-LEFT(A2,5),1)*24
Here is the break down of the formula.
Essentially, if your "Worked Times" cell value is this "8:00 - 17:00".
A) RIGHT(A2,5) return "17:00"
B) LEFT(A2,5) returns "08:00"
C) RIGHT(A2,5) - LEFT(A2,5) return "0.375"
D) MOD(RIGHT(A2,5) - LEFT(A2,5),1) return "0.375"
E) MOD(RIGHT(A2,5)-LEFT(A2,5),1)*24 return 9
#microsoft #excel #exceltips #tips #exceltricks #tricksandtips
Or if you properly have start and end times in Columns A and B, just subtract them and format as Time in Column C.
I'm having trouble getting the hang of it. Do you mind typing out what it is I have to enter to do it like you describe?
(I'm a basic factory-type drone, facing suddenly-added new responsibilities, with no experience beyond PowerPoint.)
I agree with having a start time and end time in different columns, as well as off time in the middle for lunches or appointments. Also, non-standard data sets (example times with and without the leading 0's) make me cringe.
Thank you 🙏🏾
You are so welcome
Hi, this was very helpful! Thank you. I have a question. What would the formula be if I want to add 3 shifts for 1 day? Please advise
When using military time there is no colon. Does not work if you remove the colon and reduce characters to 4 spaces. (unless you minus right from left and divide by 100.).
So if I want to calculate the sum of all hours worked in that month if it’s written in that format
You will have to do the same way.
i would like to 8 hours with 30 minutes break (start 7.30 end 16.00) include 30 minutes break. How way i calculate minus 30 minutes breaks ?
The MOD is unnecessary. It only returns the decimal portion of the mathematical operation (RIGHT minus LEFT) which is all you're going to get anyway unless the person works more than 24 hours. One day equals 1 in Excel, every hour, minute, or second is a fraction of that 1.
Thank you for your feedback! You're correct that using the MOD function in Excel is unnecessary if you're only interested in the decimal portion of the result. The MOD function can be useful when dealing with time calculations that exceed 24 hours, as it helps capture the remainder beyond a full day. However, for calculations within a single day, you can simply subtract the start time from the end time to obtain the decimal portion directly. Your point about one day equaling 1 in Excel and every hour, minute, or second being a fraction of that is accurate. I appreciate your input and insights!
@@RabiGurungXybernetics I like that you MOD solution that takes into account a wider approach ❤
The MOD is necessary to universally accommodate both day-shift hours and night-shift hours crossing midnight such as 22:00 - 03:00
What's 5 stand for
Why don't I give you a full breakdown of the formula for your complete comprehension.
Here is the break down of the formula used in the video.
Essentially, if your "Worked Times" cell value is this "8:00 - 17:00".
A) RIGHT(A2,5) return "17:00"
B) LEFT(A2,5) returns "08:00"
C) RIGHT(A2,5) - LEFT(A2,5) return "0.375"
D) MOD(RIGHT(A2,5) - LEFT(A2,5),1) return "0.375"
E) MOD(RIGHT(A2,5)-LEFT(A2,5),1)*24 return 9
I hope that helps.
When I run this formula it just give me .5... what am I doing wrong?
nvm figured it out... is there a way to get the answer in decimals like 1030 hours comes back as 10.5?
What about removing breaks?
You'd have to specify start and end time for the breaks as well.
Do you have a sample table that I can work with?
What this parameter 5 mean, plz reply?
The number 5 in the RIGHT(A2,5) denote that you get 5 characters. For example, in cell A2 of "08:00 - 17:00" when I write a formula of RIGHT(A2,5) it means from the right (or back) get 5 characters. Hence, the formula RIGHT(A2,5) will return "17:00".
Likewise, for same cell A2 of value "08:00 - 17:00", when I write a formula of LEFT(A2,5), it will yield a value of "08:00". It start from the left or the front of the string/text.
Just for clarity, if I were to change the value from 5 to say 3. For example, for RIGHT(A2,3), it will yield a value of ":00". And likewise, for LEFT(A2,3), it will result in a value of "08:".
Thanks a lot for clarification
@@debashisdasjnvp Always happy to help out.
@@RabiGurungXybernetics
❤❤❤
Thanks for leaving a comment.
Ok orrrr. Now hear me out. You can do the simple math in your head count 8 to 17. It's 8. Also normal work hours for anyone is 8 hours
Thanks for leaving a comment.
You are right, most companies operate in an 8 hours shift. However, we have quite a lot of clients that have flexible hours so that they can accommodate employee lifestyle to promote work life balance. Hence, some employee comes in at 9am but they clock out at 6pm to cover that 8 hours you were referring to. Also, we have some companies who adopt overtime policy. And this very simplified spreadsheet demonstrated in the video shows you how to calculate that working hours of each employee.
Also, most of the client we deal with have anywhere from 200 to 500 employees. And doing it manually like what you were mentioning in the comment is labour intensive and will generally can lead to erroneous data entry (about 96% of the time). We are just being human.
I hope my explanation makes sense.
Cheers!
@Khalil G No kidding these days eh.