How to Calculate Overtime Hours on a Time Card in Excel

[@jeangreen432]

Your video is the best timecard version on YT, thank you for being so thorough and an excellent instructor!

[@hectormauriciorivera]

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!

[@davidbartonjr]

Thank you!!! Your voice is clear, articulate, and easy to listen to. Your instructions are clear and precise. Thank you again!!!

[@DawnSpinhirne-Martin]

The daily overtime calculation was really helpful. How do we also add the weekly overtime for over 40 hours/week?

[@DannyRocksExcels]

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

[@casperolivier2821]

Danny.. My Formula down does not give me an accurate figure... Where as I did it lift to right it added up? PLS assist

[@DannyRocksExcels]

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

[@nonzwakaziprecious9507]

The best ever, straight forward. Love it!!!

[@hicelagonzalez8672]

Thank you so much. It was easy to follow and easy to understand. Great Job!

[@Martyr217]

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.

[@DannyRocksExcels]

@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

[@DannyRocksExcels]

@FORZA0VELOCITA Thank you for offering your feedback. I am pleased that I could help you with my video tutorial. Danny Rocks The Company Rocks

[@DannyRocksExcels]

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

[@jayephgee]

@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!

[@reycelmartinez7945]

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.

[@MrsBryant8]

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.

[@amirhosseinkanani41]

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

[@80NG5]

Can you please explain to me why we are multiplying it by 24? what's the reasoning there?

[@drummerwes81]

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?

[@FoSho21]

Don’t use the =TIME calculation. Just type 40.00

[@DannyRocksExcels]

@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."

[@joypanattil]

Made it so simple. Very helpful

[@80NG5]

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.

[@mariaisabelbrenes8183]

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

[@sabamehfooz9167]

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

[@neeta25011958]

@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

[@scottirving9853]

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.

[@palermopuertoricankitchen]

Nice Work I just subscribe to your channel, where can I download your spreadsheet.

[@amberramirez7403]

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)?

[@INCEAGLE]

=time(8,0,0) 8 stands for hour, 0 for min and 0 for sec

[@FORZA0VELOCITA]

Good sir, you just made my life a whole lot easier! Thank you!!!

[@kunsuluvivlamore1112]

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.

[@DIgitusSmartas]

need to use diff formula, IF formula to calculate hours worked that starts in one day and finished in another.

[@saifurvalli]

Great vid. Can you help me? i can't seem to get my timesheet working.

[@rahimmashaal2281]

Thanks so much. please give us info about how to calculate 4 shift on weekly and monthly base

[@DannyRocksExcels]

Thank you for your very kind words. I am delighted that you enjoyed my video.

[@markvaleros1072]

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

[@aymajid85]

a very beautiful tutorial, helped me a lot, loved it a lot, keep up the good work.

[@twanieblack]

OMG Thank you for this video OMG I think I love you smile lol AMAZING GRACE you are a GOD sent ☺️

[@oanhluong7051]

Could you please do the video about calculate minimum wage in crease for student employees? Thanks

[@atifiqbal5310]

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

[@billh8559]

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.

[@EJ-gf2qp]

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

[@boarder14]

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.

[@80NG5]

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.

[@LeoHernandeznyc]

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?

[@ibite74]

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

[@waynepiper720]

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

[@nadhiral-ani8524]

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.

[@nadhiral-ani8524]

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 ?

[@royalbengaltours]

Really really helpful. Thank you very much.

[@ohioassociationofthedeafse4932]

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.

[@michaelfokias]

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

[@ryancampbell7720]

straight forward this helped a lot.

[@pramodparnami6012]

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 🇮🇳

[@DannyRocksExcels]

Hi Matthew - The solution is to change the formatting for the cell which contains the formula for adding up the number of hours worked. Change this cell formatting to [h]:mm - You do this via the Custom Formatting selection. From there, select a pre-built custom format and type [h]:mm 24 + 19 = 43. Without the [h]:mm formatting, hour 25 becomes 1 ... hour 43 becomes 19, etc. Danny Rocks The Company Rocks

[@eyes2bj]

Thanks a lot, very helpful

[@darrylpoe24]

@Danny Rocks How do you split the overtime and double time? and anything over 40 hrs a week?

[@Mommau5]

@danny Rocks I could use this also, since I worked 7 days a week.

[@erwinvdb]

Wonderful !!!! Thank you sir!

[@DannyRocksExcels]

@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

[@rukiboninay]

what if the break is paid is it gonna be the same formula to get the hours of worked?

[@DeepakEduWorld]

Good job 👍

[@eadminsls5752]

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

[@Sur5_lb988]

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??

[@amaliabalderama5088]

What about working from 8:00 am until 1:00 am ? i did the formula but it says "Dates & Times that are negative or too large to display". need help.

[@amaliabalderama5088]

oops.. i got it..i know what to do. ^_^

[@jackychawla5422]

i know it

[@ctabs1]

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

[@mmutebiclinton5865]

I like the that and easily explanatory

[@ajgomez6043]

Thank you video from ten years ago :D

[@КрижановськаТетяна]

If I worked less as a regular day, like 6 hrs? Which formula is?

[@elizabethbassey2248]

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

[@MaCherieMcAli]

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?

[@insane9606]

Custom format- [H]:mm

[@drummerwes81]

@@insane9606 that still didn't fix the issue

[@fenix0green]

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?

[@GeorgeAJululian]

thank you sir very helpful

[@DannyRocksExcels]

@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

[@zeeshanalikadri]

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

[@epark9925]

How would you modify the formula for worked hours if you put 7:45 AM but your actually start time is not supposed to be until 8:00 AM? You don’t get overtime over for starting work at 7:45 AM?

[@DannyRocksExcels]

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

[@papadakis1981]

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.

[@TheKesmen]

This was super useful, thank you vary much :)

[@VinayYadav-xf7vg]

If you want to calculate someone's short time in this, how will you do it?

[@julycesar4913]

Good instruction, thank you.

[@futuresuperstar4life]

Can you calculate overtime by week and not by day??

[@ram139100]

Fantanstic Information ..thanks to the uploader !!!

[@marko5451-v5p]

How do you calculate breaks if you only get a break after 6 hours work, and over time if you only work over 8 hours a day.

[@raheembashag3724]

The video was great what if a staff gets absent how should I calculate

[@HaithamCV]

if i have over time of several minutes every day, what should be done in this case ? add them all together ? or what ?

[@wxmyjnsn]

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

[@jamesmacdonald3763]

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?

[@controlhouseinternational3611]

Thanks for this video. keep it up!

[@nitinsingh.2107]

Hi! How can we add the condition for under-time as well in the same formula

[@HODLHAHA]

How do we get the formula to subtract 30 minutes for lunch if over 6 hours are worked?

[@youtubediscriber6438]

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

[@StillOnTrack]

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?

[@Mrautomatom]

Hey, what if I need like a fixed rate for up to 9 hours and overtime by the hour?

[@Smartliving-udanagayan]

This is Good , Thank you , But how to calculate after mid night worked over time >?

[@gamingwithdarkfang]

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

[@freshjuice9696]

very helpful , thank you

[@hugginsm1]

The total of 43 still keeps on showing 19 im doing everything you say but still stays on 19 any suggestions.? Thanks

[@ahmedayinde4285]

Can we have this in excel that we can download

[@abuzarkhan619]

Very Helpful... , ,, , , Thank you Buddy

[@iamthenewsuperhero]

I want to ask on the Pay Part, why do you have to multily the total number of hours worked to 24?

[@gwempren]

How would i calculate double time

[@suranjitbarua1719]

Please upload the video along with exercise file.

[@jayephgee]

@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

[@damientammyingram461]

Open Excel, click 'New' search for the template 'checkbook' there are so many great templates for this.

[@sashikakrishpeiris1228]

Your video was very helpful , please help me to calculate: Total hrs worked per day - 9hrs (regular working hrs )= Overtime hours X O.T. pay rate. please create a propper formula to use on my worksheet , also if an employee is failed to enter his Time In or Time Out the cell must show a Zero. Total hrs & OT hrs must show a zero or any other valid symbol which does not effect my other calculations. I used this formula: =IF(B3>C3,1+C3,C3)-B3 but when there is a zero value on a cell the answr wrong

[@ashleytarter2493]

@ 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

[@anadubon3011]

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.

[@gamingwithdarkfang]

having same problem, have no idea on how to do it either... could use help there too

[@DIgitusSmartas]

@@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

[@monicacastaneda29]

What if there was over time on a Saturday then what is the formula?