How to Calculate Overtime Hours on a Time Card in Excel

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

[@jeangreen432]

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

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

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

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

@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

[@DawnSpinhirne-Martin]

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

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

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

[@nonzwakaziprecious9507]

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

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

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

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

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

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

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

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

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

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

[@hicelagonzalez8672]

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

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

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

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

[@saifurvalli]

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

[@joypanattil]

Made it so simple. Very helpful

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

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

[@FORZA0VELOCITA]

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

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

[@rahimmashaal2281]

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

[@DeepakEduWorld]

Good job 👍

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

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

[@oanhluong7051]

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

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

[@mmutebiclinton5865]

I like the that and easily explanatory

[@ryancampbell7720]

straight forward this helped a lot.

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

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

[@GeorgeAJululian]

thank you sir very helpful

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

[@erwinvdb]

Wonderful !!!! Thank you sir!

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

[@eyes2bj]

Thanks a lot, very helpful

[@twanieblack]

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

[@ajgomez6043]

Thank you video from ten years ago :D

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

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

[@royalbengaltours]

Really really helpful. Thank you very much.

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

[@palermopuertoricankitchen]

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

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

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

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

[@ram139100]

Fantanstic Information ..thanks to the uploader !!!

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

[@aymajid85]

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

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

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

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

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

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

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

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

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

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

[@berhanearayaselasie9913]

Very usefull thank u

[@julycesar4913]

Good instruction, thank you.

[@raheembashag3724]

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

[@freshjuice9696]

very helpful , thank you

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

[@hugginsm1]

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

[@DannyRocksExcels]

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

[@VinayYadav-xf7vg]

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

[@futuresuperstar4life]

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

[@nitinsingh.2107]

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

[@ahmedayinde4285]

Can we have this in excel that we can download

[@iamthenewsuperhero]

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

[@friendscricle.4281]

Very nice sir

[@TheKesmen]

This was super useful, thank you vary much :)

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

[@HaithamCV]

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

[@controlhouseinternational3611]

Thanks for this video. keep it up!

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

[@Mrautomatom]

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

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

[@mahmoudahmed2539]

Many thanks

[@MS-eo6ud]

Thank you ❤

[@abuzarkhan619]

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

[@Smartliving-udanagayan]

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

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

[@gwempren]

How would i calculate double time

[@marklamberton541]

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.

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

[@ashannavemula4134]

Great 👌 posted

[@sankalp2011]

i tried to make regular hours by applying hh:mm but instead it comes as 21:20 P.m , formating cell is not working and I am using office2007

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

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

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

[@deepikadulanjani972]

Thank you