How to Calculate Overtime Hours on a Time Card in Excel
ฝัง
- เผยแพร่เมื่อ 25 ม.ค. 2011
- A viewer asked me to show him how to calculate the number of Overtime Hours Worked and the Payment for those hours on a Time Card in Excel.
Hew was frustrated - along with most Excel users - because the formulas he used did not produce the results that he was expecting. Excel is VERY picky about the FORMATTING that must be supplied to the cells that calculate the differences in times and the totals for times that exceed 24 hours.
Watch, as I demonstrate how to work with Time Functions, Formulas and Formats in this lesson.
I invite you to visit my website - www.thecompanyrocks.com - to learn more about the resources and services that I offer.
Danny Rocks
The Company Rocks
Your video is the best timecard version on YT, thank you for being so thorough and an excellent instructor!
@Danny Rocks
I wanna thank you very much for this step-by-step instructional video on this! It helped me tremendously on trying to figure out my timesheet and my total hours worked, with overtime added. Thank you again!
Danny Rocks is awesome for this daft programme, I haven't used excel since High School (That was 15 years ago) and these short tutorials really help.
Wow...!! Finally a video that it's understandable and straightforward. I was trying to find the formula to calculate exactly this and I found it. Thank you!
Thank you! I was always frustrated with it not totaling the hours right - I did not know about the [h] function. Also did not know how simple it was to convert total [h]:mm to a decimal without splitting it and dividing minutes by 60. Thank you.
Thank you!!! Your voice is clear, articulate, and easy to listen to. Your instructions are clear and precise. Thank you again!!!
This is amazing. I literally want to thank. All my queries are solved about over time hours to calculate. it's really useful piece. thanks alot again
Hi June -
This is not a problem. I am happy that you figured out how to answer your question.
In my experience, I have found that it takes me - on average - about 4 re-readings / viewings and @ 10 recreations of an exercise in order to fully understand a new concept in Excel.
It is nearly impossible to acquire a new skill after just one viewing.
Thanks again for your feedback.
Danny Rocks
The Company Rocks
Thank you so much. It was easy to follow and easy to understand. Great Job!
Good sir, you just made my life a whole lot easier!
Thank you!!!
Thank you! I am glad that you enjoyed my tutorial.
Sorry for the delay in responding - I missed the message notification.
Thanks for adding your feedback.
Danny Rocks
The Company Rocks
Danny..
My Formula down does not give me an accurate figure...
Where as I did it lift to right it added up?
PLS assist
The best ever, straight forward. Love it!!!
a very beautiful tutorial, helped me a lot, loved it a lot, keep up the good work.
straight forward this helped a lot.
Made it so simple. Very helpful
@KraftyGrandma
Thanks - I am pleased that I could help you.
re: seeing a Minus Time Value - Excel does NOT like this. In fact, if you perform a Time Calculation that results in a negative number, Excel places ######## symbols in the cell!
My solution is to Label 2 cells 1) Hours worked over 37.5 and place an IF Statement there. 2) Hours worked LESS than 37.5 and put an IF Statement there. Both formulas will return POSITIVE Numbers / Hours.
Change formatting for cells as needed.
Danny
Really really helpful. Thank you very much.
This was super useful, thank you vary much :)
Thanks for this video. keep it up!
Good job 👍
Good instruction, thank you.
thanks a lot
you solved my problem. I tried 2 days to convert 29:55 (over time for a month) to a decimal number and learned form you to multiply to 24.
so simple trick.
thanks again
Can you please explain to me why we are multiplying it by 24? what's the reasoning there?
The daily overtime calculation was really helpful. How do we also add the weekly overtime for over 40 hours/week?
Very Helpful... , ,, , , Thank you Buddy
Thanks for your very kind words June. I am pleased that I could help you.
I am even more pleased that you could figure out the solution to your question before I had a chance to respond!
Thanks again for contacting me.
Danny Rocks
The Company Rocks
Hi Danny thank you very much for you informing video. 1 question if I may? Is it possible to to write a formula so that if an employee doesn’t complete an 8 hr day that it is supplemented by overtime hours first before paying time and a half? Hope this makes sense.
Fantanstic Information ..thanks to the uploader !!!
Wonderful !!!!
Thank you sir!
Hello Danny
Excellent video by the way and a great help to one of the major issues i have had with excel and the calculating of overtime hours.
is it possible to take it one step further and using the same sheet as your example to calculate how many hours at double time if the 1st 2 hours after a regular 8 hour day are at time and a half, as they are in your example above, how would be the best way to reflect the hours over 10 in a spreadsheet to be paid at double time?
bit of a newbie when it comes to excel, i know what i want to do, but lack the expertise to do it. can you please help me with this issue?
very helpful , thank you
Excellent video, I thank you for the information. Can you please make one where the case of working Saturday and Sunday take please since that will change the format and also the way the overtime is calculated. I try this way with the weekend but is not working quite well. Thank you again.
Thanks a lot, very helpful
@FORZA0VELOCITA
Thank you for offering your feedback. I am pleased that I could help you with my video tutorial.
Danny Rocks
The Company Rocks
thank you sir very helpful
Thanks so much. please give us info about how to calculate 4 shift on weekly and monthly base
I did this and it worked perfect for total overtime but how do I calculate 1.5 x standard hrs overtime and 2.0 x standard hrs overtime. I've been playing around but can't get it to work. we work a 7.36 hrs day as standard the first 2 hrs overtime is 1.5 x standard and every hour over that is double time. thanks
Very usefull thank u
can you please make a vidoe on two time break like refreshing break in and out and lunch break in and out .and how can we calculate late check in time .or late lunch break off time
I like the that and easily explanatory
Thank you on this video. but how about if we want to separate night differential working hours to regular number of hours and over time hours
thank you Danny, this worked really well. However, I work in film and we often go into overtime after midnight. How can I enter after midnight hours (Ex 2:30) and have excel think logically that I mean it's a time after 24:00? I can manually enter the date in my start and end cells to show the days are consecutive but I want to be lazy and not do that :D many thanks
Awesome stuff thank you. Can I please get the reasoning behind the multiplication by 24 when trying to convert hours to a number? I need to be able to explain why the results sometimes differ in decimals.
I read that *24 is what changes the time format into a decimal format that is easier to read and can be used in mathematical calculations, such as for wages. For ex:
8 hours and 30 minutes becomes 8.5
8 hours and 45 minutes becomes 8.75
Hi! How can we add the condition for under-time as well in the same formula
OMG Thank you for this video OMG I think I love you smile lol AMAZING GRACE you are a GOD sent ☺️
Many thanks
The video was great
what if a staff gets absent how should I calculate
Thank you ❤
What if I want to have two big catchment cells reflecting an entire week's cumulative regular and overtime hours (once cell to catch each)? Would I have to do the same thing only feeding the data into individual Regular and OT then adding those into my cumulative cells? Or can I do it all in one formula bypassing the need to display ever day's Reg/OT?
I basically want IF MondayHrs > 8hrs, THEN (MondayHrs - 8hrs) + OTCatcherCell, AND TuesdayHrs > 8hrs, THEN (TuesdayHrs - 8hrs) + OTCatcherCell, AND IF WednesdayHrs > 8hrs, THEN (WednesdayHrs - 8hrs) + OTCatcherCell.... etc. Then I also need something similar for the regular hours worked. I don't know if there's a way to do multiple AND conditions to add it all into one formula. Any tips?
hi Danny I just saw this video and after watching it was left wondring is there any way to cap a cell to a maxuim number here in australia we have a a 2 teir overtime system 3 hours 1.5 and double time after that. how could i set a maxium of 3 hours in the first coloum and what would be the formula for the second to pick up the extra time. thanks
PS: A big thanks for taking your time to make these videos they are a hugh help for novises like me
May i inquire Sir:( I keep getting an error message) when attempting to create regular and overtime hours, it reads: The formula you typed contains an error. To get assistance in entering a function, click OK,then click Function on the insert menu. If you are not trying to enter a formula, avoid using an equal sign(=) or minus sign (-), or precede it with a single quotation mark ('). (end error message). I am using Excel version 2013. After I input the data and hit "control - enter" then I get this error message. Grateful for any insight thanks Jon
Thank you video from ten years ago :D
when i use format painter to copy it in REGULAR when ill drag down it only put 8:00 all the cell...how u do that it will make your REGULAR make it change in every cell? do u have any button r u using for or any formula to compute the REGULAR
@neeta25011958
Thank you. I am pleased that you enjoy my video tutorials.
Re: "Minus" time or "Negative Time Values" - Excel does NOT like them. If you attempt to enter a Negative Time Value or use a calculation that produces "negative time," Excel will fill the cell with an unlimited number of ########. For example, calculating time that "Spans Midnight." for a "Graveyard Shift" or a "Red-eye" airline flight.
Re: A "Label" that is simply a text entry such as "Start Time."
Hey, what if I need like a fixed rate for up to 9 hours and overtime by the hour?
This video was uploaded 10 years ago,
As Boris Johnson said there is One Sun, One planet, One Grid.....
I say there is Only One Danny that Rocks,
Best regards from the Land of Mr. Modi 🇮🇳
Thank you for this video, it was most helpful out of all the other videos I watched. One question please. What is the formula for the "Standard Work Week" (under "Standard Work Day" in Column J Cell J2)?
=time(8,0,0) 8 stands for hour, 0 for min and 0 for sec
@Danny Rocks
Do you have any videos showing how to create an excel spreadsheet to keep track of debits and credits, similar to a checkbook register? I'm really curious of how to make one, and what specific formulas to use to make it work on its own. Thanks again for all your amazing videos! :D
Open Excel, click 'New' search for the template 'checkbook' there are so many great templates for this.
Thank you
Thanks!
In CA OT is calculated both more then 8 hr is a day and more then 40 hrs in a week. Any ideas?
ThanX
One hour Over Time rate is calculated based on a major parameter: Per day work hours. As per current rule, 8 hours are working hours for most of the businesses.
very good tutorial but i have a slightly different issue I am trying to do a sheet for people on the overnight from 11pm until 7am, thanks to your videos I have been able to get that info, but we pay overtime not for over 8 hours but for anything over 35 hours in a week. I tried using the time function on the standard work week but that didnt work. Can you help me please?
Thanks Lot sir
This is Good , Thank you , But how to calculate after mid night worked over time >?
I figured out how to calculate overtime for the week for over 40 hours.... the Time function goes up to 24 hours, in one cell, I put =TIME(20,0,0) and then the cell next to it I put =TIME(20,0,0), in the next cell, I added the 2 cell together with the [h].mm format and it give me 40 hours as the reference point. Use this cell as the reference point and anything over 40 hours will be calculated as overtime just like the daily overtime hours is calculated.
@ Danny rocks I need help with time card for Nevada the law is anything over 8 in a 24 hour period plus 40 in a week with 2 different jobs for same employer also converted in to decimal wage reg is 8.25 to is 12.38 can u help
wow thank you
what if the break is paid is it gonna be the same formula to get the hours of worked?
Great 👌 posted
Thanks
Very nice sir
@DannyRocksExcels Dear Danny your video is a great help, however like kraftyGrandma I simply dont understand how to do the minus part. Do you have a video showing me how to do the solution to label the 2 cells. You are heaven sent and thank you for your time and efforts you put to help us..pls help me do the time sheet with the minus...am struggling thank you and god bless
Excellent tutorial, however, I have a problem. When I enter 40 underneath 8 for standard work week, it converts to the 40 into 16. How do I fix this?
Don’t use the =TIME calculation. Just type 40.00
@Danny Rocks
How do you split the overtime and double time? and anything over 40 hrs a week?
@danny Rocks I could use this also, since I worked 7 days a week.
what if employees have different check in times? how can I calculate thier overtime? could you help please?
Hello I Work from 16.10 - 1.30 or 2 or 1 but the problem is from 16.10 -20.00 i have lower paymant and after 20 : 00 - 1:00 or 1:30 i have biger paymant couse of night time
how to calculate this im trying but im not geting it :(
thnx
Thank you so much, this is very helpful!
:( formula doesn’t work for night shifts, for example if it is time in at 11:00 pm out for lunch at 3:30 am - back from lunch at 4:30 am and time out is at 7:30 am.
It gives you negative outcome. Error note says dates and time that are too large or negative display as #############
Please help. It has to be + or - 24h to turn that negative outcome into positive, I’m not sure how to incorporate it into formula.
need to use diff formula, IF formula to calculate hours worked that starts in one day and finished in another.
How do I calculate 9:00 to 2:00pm? I wrote the formula but how to I hide the pm so it displays 9:00 - 2:00?
Great vid. Can you help me? i can't seem to get my timesheet working.
Could you please do the video about calculate minimum wage in crease for student employees? Thanks
I am in need of a spread sheet that calculates straight time to over time back to straight time, can anyone help me with this
I need to know how to enter a formula that will calculate total time with OT and vacation/holiday pay.
For example: Person is paid biweekly. Week 1, works 42 hours. Week 2, works 38.5 hours and uses 4 hours vacation time. Their total hours should be 78.5 hours regular time, 2 hours OT and 4 hours vacation. So 82.5 hours paid at regular time rate and 2 hours paid at OT rate. I can't figure out how to have the spreadsheet only calculate the OT hours based off of 2 hours and not include the additional 2.5 vacation time at OT rate. Can you help??
1st) Ensure that your hours are correctly entered as numbers (they automatically align to the right-side of the cell.
2nd) Write the formula = cell with end time (8:00 PM) - cell with start time (4:00 PM)
3rd) You will probably need to change the formatting for the result cell to h:mm
Danny Rocks
The Company Rocks
Was feeding time start and end as 10:30 PM - 12:00 AM, shows total hours -22.5. I tried looking into the comments by viewers to see if they have the same problem and hoped to see if there's another formula to fix this ... I see you haven't answered their questions since your video was published.
@sankalp2011
21:20 P m - That is a "TEXT" entry. If you want to enter 9:20 in the evening, enter 9:20 (space)p and Excel will convert that to 9:20 PM - Do NOT use any .(periods) and do NOT include and (space) between P M. If you do, then you will get a TEXT result.
Are you trying to calculate the number of hours worked? If so, send me some additional information and I will try to help you.
Danny Rocks
The Company Rocks
Hi Danny .. great video .. but just asking how to deduct hours.. lets say I toke 3 or 4 hours unpaid leave .. how to calculate negative working hours or -ve OT ?? Thanks in advance.
Sorry .. i sort this out .. and I have another Q .. how to relate weekdays like Friday & Saturday to a certain value i. e. in Payment sheet how can automatically detect weekends and not deducting payments ?
Hi Danny,
Thanks for the video,
I have a question I am using openoffice and when I goto convert to time to a number it always show incorrect eg. say I worked 1:15 overtime, I do the =g10*24 but it will show as 1.25 not 1.15 as it is meant to
I would like to ask :
If the working hours are less than standar for example on Thursday.How can the result be minus 30minutes on overtime column?Because i want to calculate at the end how many overhours i have..The reason is that i dont get paid bach the overhours.I just get an extra day off.
Hi Danny.
Thanks for this amazing video.I have a one question for you.Why the working hour is change.Like my working hours are 39:30 why it become 39:50 same for the overtime too.what happen to 20 sec is it OK to avoid 20 sec on payroll? Thanks again
Please am finding it difficult to format a time stamp I copied from an app, I have to active the cell one by one. Please is they any other way it can be done
How to create the standard formula for 40 hrs a week? I saw the standard formula for time but I don't know how to do the one for 40 hrs a week.
having same problem, have no idea on how to do it either... could use help there too
@@gamingwithdarkfang That is just number entry as in no formula, as far as I could have decipher it as it it is not used in any calculations. I'm not even sure why it was there but probs from a previous template
Great Video! Do you know a way to add a calculation for Saturdays and Sundays? Saturdays would be overtime all day, and Sundays would be doubletime all day.
What I did was for weekends I don't split the hours between regular and overtime, instead I take the entire hours worked that day and add them to overtime because they use the same rate.
if i have over time of several minutes every day, what should be done in this case ? add them all together ? or what ?
I am trying to add the 40 hours and I add 40,0,0 and ends up giving me 16 hours. Any thoughts of what I am doing wrong?
Custom format- [H]:mm
@@insane9606 that still didn't fix the issue
I did the exact why you showed but instead of getting 3 hrs overtime it shows 16:00.
here is the totals: total hrs 11:00, giving it 8:00 reg meaning it should show 3:00 overtime
I am having a hard time, my standard week hours is 50, and when i enter the custom format [h]:mm and the formula =time(50,0,0) it gives me 2:00. how can i get this to say 50 hours is my standard work week
Great video. I would like to throw a wrinkle in there. For reasons too long to explain here I need to convert the OT hours to regular hours and then calculate pay. Excel is giving me problems when doing this. For example a worker worked 65.hours at 9.25 and 7 hours of OT at 12.38 so his pay should be 687.91. NOW, if I convert the OT hours (7*1.5=10.5) and I add them to the regular hours 65 + 10.5 = 75.5 * 9.25 I get 698.38
Pls Example file in distribution for excel
Format
Sir how to calculate late timing.
The total of 43 still keeps on showing 19 im doing everything you say but still stays on 19 any suggestions.?
Thanks
@KhurrumIqbal1
Give me another day or two to create a video that demonstrates this for you. Calculating time that "crosses midnight" presents a particular challenge. Fortunately, there is a formula that will perform the correct calculation.
To be clear: you want to count "any work hours" above 8 hours in "any 24 hour period" to be considered as overtime.
Is that correct?
Danny Rocks
The Company Rocks