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!

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

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

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

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.

@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

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

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!

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.

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

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

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

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

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

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

Good job 👍

Really really helpful. Thank you very much.

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

Good instruction, thank you.

straight forward this helped a lot.

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

Thanks for this video. keep it up!

Made it so simple. Very helpful

This was super useful, thank you vary much :)

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

3 жыл бұрын

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

Fantanstic Information ..thanks to the uploader !!!

Wonderful !!!! Thank you sir!

very helpful , thank you

thank you sir very helpful

Thanks a lot, very helpful

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?

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

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.

Very usefull thank u

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

Many thanks

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

@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

5 жыл бұрын

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

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

@James_Tywinn21

2 жыл бұрын

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

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.

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.

I like the that and easily explanatory

Thank you

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

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

Very nice sir

Thanks!

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

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

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

Thanks Lot sir

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

@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

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

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

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

@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

In CA OT is calculated both more then 8 hr is a day and more then 40 hrs in a week. Any ideas? ThanX

@manur.k9048

6 жыл бұрын

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.

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

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

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

wow thank you

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

@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

Thank you video from ten years ago :D

Great 👌 posted

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

Thanks

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

@Mommau5

5 жыл бұрын

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

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?

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

3 жыл бұрын

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

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

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?

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.

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

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

3 жыл бұрын

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.

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

8 жыл бұрын

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 ?

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

4 жыл бұрын

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

@DIgitusSmartas

3 жыл бұрын

@@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 vid. Can you help me? i can't seem to get my timesheet working.

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

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

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

Pls Example file in distribution for excel Format

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

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

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

@ 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

what if employees have different check in times? how can I calculate thier overtime? could you help please?

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

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

3 жыл бұрын

Custom format- [H]:mm

@drummerwes81

2 жыл бұрын

@@insane9606 that still didn't fix the issue

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?

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?

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

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

Sir how to calculate late timing.

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

Please upload the video along with exercise file.