Your Beauty of explaining lies in: 1. Your soft loving tone 2. Professional expertise and 3. The care you have for us humans Thank you Danny Rocks! Hope you are successful & have the best of health and long life
13 years after you posted this video it is still pertinent. After 20 minutes of trying to make excel calculate time difference I found your video and solved the problem in 20 seconds. Thanks a bunch!
This is so useful that I am book marking it. The time functions and formatting have been driving me nuts. This is so clear, to the point, no waffle. Best video of the week by far. Many thanks.
Oh man have I wasted some time on this time! An incredible period has elapsed. I could not ever ever have done this without you help. Thank you so very very much.
New to Excel, I encountered every problem in setting up a time schedual. With your video it was a breeze. I asked several IT personnel with no help. Thanks for the great and informative video!
Had to swipe through 4 videos on time difference calculation before reaching here which solved my frustration #3 !! I was calculating the difference between two times keyed in 24 hours format (without AM or PM stamp)with the end time crossing over midnight, custom format + MOD formula fixed the agonizing ########## ! Thanks a ton Dan! :-)
it was quite useful....ur video showed the querry which many so called experts avoided...i.e., When AM & PM both are given .... when departure time is in AM and Arrival time is PM.... "Eureka"
Thank you so much for doing this. I watched four video prior to this and could not figure out why my time calculations were not working. I did not realize there had to be a space between the number and the AM/PM. Works perfect now
Thanks...Very useful...I was becoming crazy at work trying to calculate the average of several times....Now I've changed the format and have the answer I needed..
THANK YOU!! I was trying to create my time sheet for some freelance work I have been doing and could not get any other formula that I found on the internet. This was because I worked from 11PM to 1AM and none of these formulas were made to calculate time that went past midnight. This video has been a great help in ending my frustration with my time sheet.
So thankful for your video. I was so frustrated with the time sheet I had created and it was such a tiny error. I didn't space between my time and AM/PM. Learning how to format the cells correctly was exciting too! You explained it so well! Thank you!
Thank you Vicki. I am pleased that I could help you to understand how to enter and calculate Time Values in Excel. As you learned, Excel requires a strict syntax when entering Time Values! I appreciate your feedback. Danny Rocks The Company Rocks
From your explanation I was able to resolve the total sum of time in any array. My frustration while creating a Timesheet was; excel tends to round-off values over the 24hr clock into 1 Day rather than returning a value greater than 24:01:00 for hourly billing.
Thanks. the hint on using the custom format fixed my problems with trying to calculate years. I was using the date format on the column for subtracting two dates and that was showing 1904 as the year with is correct if your counting the number of days between start date and end date and you start at the year 1900.
Thanks so much Danny! Your rock! (Excuse the pun). You just saved me hours of wasting time trying to solve this with the Excel help files. Your video is excellent and so helpful. I wish you much success and prosperity for the future.
Thank you so much for this tutorial, I was beginning to get frustrated trying to calculate time until I found this video. Once again thank you so much it saved me lot of time and lots of energy.
Wonderful, it is indeed a great video. It would be great to have the Excel file shown in the video, it will help me to practice. Can you please give the link to download the excel file... Appreciate your kind understanding. Sanjeeb
@MrPrios1 Thanks! I am pleased that I could help you. Here is a quick thougt for your Date / Time question: 1) Enter =NOW() into a cell to get the current date and time in 1 cell. 2) Copy this and Paste Special Values to turn it into a constant (this is an easy way to ensure that you have the correct format for date and time) 3) Edit the Date and the Time in the Cell or in the Formula Bar to get your Starting Date / Time. From there you can "increment" the date & Time as you wish. Danny
@johnmaxvan Great! I am pleased that I could help you with this. Calculating Time differences in Excel can be frustrating - the details really count. Danny Rocks The Company Rocks
Hello Danny, thanks for this great tutorial. Do you have a follow on tutorial that shows the formula for taking out the lunch portion of the day? Thanks and LMK when you can.
I was going crazy trying to sum a list of times. [h]:mm saved me, so simple but not obvious. Excellent video with the visuals and good explanation. Thank you!
Hey, Really wanna thank you for the effort you put here, you are the KING of Excel. There is any video about time calculation in 24h-format? Thanks in advanced
@Pitahchez Yes, in order to display the total amount of hours worked, you MUST apply a Custom Format to the cells with the Total Hours worked - Use [h]:mm This is the biggest challenge when working with Time Calculations. For Custom Formatting, Right-mouse-click the cell - choose Format Cells. On the Number Tab, Choose Custom from the Category and then type in [h]:mm Danny Rocks The Company Rocks
My pleasure! Understanding how to correctly calculate Time Intervals in Excel can cause much frustration, I am glad that I could help you. Danny Rocks The Company Rocks
Danny Thanks your instruction was vary easy to follow and that Mod() function is fantastic. I knew as soon as I heard you speak that you must do this professionally. You do really Rock thanks again. Robert
Hello Charles - I apologize for the delay in responding. I am pleased that my Excel Tutorial helped you with your problem. Danny Rocks The Company Rocks
Hi Anthony - I tried to recreate your question and here is what I suggest: 1) Enter your values using this syntax: 00:00:30 for Thirty Seconds. 2) Notice that when you enter 00:00:60 for 60 seconds, Excel formats it as 00:01:00 (1 minute) 3) If you do not want to see the Hour Component, simply format the cells using the custom mm:ss mask. Excel is very rigid when it comes to accepting "Time Value" entries. Use the hh:mm:ss syntax for entering seconds. Danny Rocks The Company Rocks
oh my goodness! best help! I'm still having some issues with the elapsed time. Say I start something at 8am only want to spend 30 minutes but go over by 15 - how would I add or subtract the time with that function. Great help! Thank you so much!
@InnerOrchestra Hi - Thanks for posting your comment. I have to admit that I have not yet watched "The Matrix" movie, so I will have to rent it. I am pleased that I could make this a "fun" video. Learning should be fun in my opinion. Sincerely, Danny Rocks The Company Rocks
I checked this video three times now and fixed most of my problems with those simple tips - thank you! I have another problem: I have a date and I want to calculate the number of hours due on that date, so it would be something like if +7 then add 30:24 but I cannot figure out how to do it. Each week adds another 30:24 due. One (30:24) is always at the same place, the other one is moving down the column. Can someone help me? Nicole from Canada
To calculate the amount of time that has elapsed, use this formula: Ending Time - Starting Time. So, in your example, 7:00 PM (Ending Time) - 3:45 PM (Starting Time) is 3:15 - Three Hours and 15 minutes. Be sure to format the cell for your result as "h:mm" There you go! Danny Rocks The Company Rocks
Yes sir i am trying to calculate straight time from total hours how do i do this please my total hours is 7.50 how do i get straight time please thank you
Nice to find a video that delivers five minutes' worth of content in five minutes as opposed to fifteen. Thanks! Also, you're still intelligible at 2x :)
I would advise there are 5 areas of frustration. You actually touched on some issues that bothered me long ago. The 5th area of issue would be time conversion like MT to ET or vice versa; this goes hand in hand when you were using "flights" as an example. Adding 2 hours: =A1+"02:00" if you had 22:00 in A1, the formula would show 00:00. But if you needed to go from ET to MT, the formula would be =24+A1-"02:00" 24, represents 24 hours in a day, if you put 00:00 in A1 it would return 22:00.
Your Beauty of explaining lies in:
1. Your soft loving tone
2. Professional expertise and
3. The care you have for us humans
Thank you Danny Rocks! Hope you are successful & have the best of health and long life
13 years after you posted this video it is still pertinent. After 20 minutes of trying to make excel calculate time difference I found your video and solved the problem in 20 seconds. Thanks a bunch!
You are literally the only person on the face of the earth that can explain this clearly. Thank you!
This is so useful that I am book marking it. The time functions and formatting have been driving me nuts. This is so clear, to the point, no waffle. Best video of the week by far. Many thanks.
OMG. I've been searching this for almost 3 months. Now I've found it! Thank you so much!!!
I love the tutorial. It's right to the point and you make it easier to understand than most tutorials. Thank you!
After 12 years of uploading this video... It still helped me :) Thanks a ton!
THANKS FOR TIP.
Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks
Thank you for very clear instructions that are easy to understand and follow. I am now going to look for more of your videos.
Oh man have I wasted some time on this time! An incredible period has elapsed. I could not ever ever have done this without you help. Thank you so very very much.
What a time saver! Short and to the point!
New to Excel, I encountered every problem in setting up a time schedual. With your video it was a breeze. I asked several IT personnel with no help. Thanks for the great and informative video!
This was a great tutorial. It helped me to quickly resolve my time calculations. I was very frustrated prior. Thank you!
Had to swipe through 4 videos on time difference calculation before reaching here which solved my frustration #3 !! I was calculating the difference between two times keyed in 24 hours format (without AM or PM stamp)with the end time crossing over midnight, custom format + MOD formula fixed the agonizing ########## ! Thanks a ton Dan! :-)
Thank you. The simplest mistakes can cause the biggest problems. Thank you for this quick tutorial.
Thank you for this video! You've clarified matters in such a simple way.
You are a life saver, watched some of your videos already and spot on fix to my excel nightmares. THANK YOU.
Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks Danny!!!
This is the best explanation for time calculation problems in Excel. Thanks a ton for this great video!
straight to the point, no lengthy introductions!! thanks a lot
TP014563
it was quite useful....ur video showed the querry which many so called experts avoided...i.e., When AM & PM both are given .... when departure time is in AM and Arrival time is PM.... "Eureka"
DANNY ROCKS !!!!! THANK YOU.....THOSE BRACKETS MAKE A LOT OF DIFFERENCE...PROBLEM SOLVED.
Thank you so much for doing this. I watched four video prior to this and could not figure out why my time calculations were not working. I did not realize there had to be a space between the number and the AM/PM. Works perfect now
Hi Danny, your video helped me solve a problem that was not shown in the video but gave me some valuable tips on formatting, thank you.
Thank you for the Time Formulas, it really works in doing the calculations of adding worked hours of a week....Marvelous Mr.Rocks
OH MY GOD!!!! i have been trying to find the formula for adding total hours. Thank you!!!!!! I love you.
Hello this is such a life saver!. Thank you for sharing your knowledge God Bless and more power!
This 12 years-old vídeo is a legendary to me! It saved-me right now as well!!! =D
Excellent teacher. Your videos are so clear and easy to follow
Thank you Sir , I was looking the minus two time between different days around. It is very easy and help full. Thanks
Loved this. Great tutorial. I was having frustrations with times crossing midnight. Saved me a bunch of time!
De nada! My pleasure. I am delighted that I could help you.
Thank you for adding your comments.
Danny Rocks
The Company Rocks
Thank you so much for your instructions. Very clear and concise. I was having trouble calculating my time sheet, now I'm good to go.
This was INCREDIBLY helpful!
Danny, you rock alright! I cannot believe how convoluted the other youtube tutorials are compared to yours. Thanks!! Steve
Thanks...Very useful...I was becoming crazy at work trying to calculate the average of several times....Now I've changed the format and have the answer I needed..
Thank you, half way through I got the answer I needed. Cheers
Thank you so much. I was so frustrated until I watched your video!
Thanks for the tip.
One of the most important things in Excel: FORMATING FORMATING FORMATING (Ctrl+1)
You are amazing
THANK YOU!! I was trying to create my time sheet for some freelance work I have been doing and could not get any other formula that I found on the internet. This was because I worked from 11PM to 1AM and none of these formulas were made to calculate time that went past midnight. This video has been a great help in ending my frustration with my time sheet.
I wonder, who are these 9 dislikers?
Such an important information he is giving. Thanks a lot.
This is a life saver, been my pulling my hair out over this. Just could not get it to work. thank you thank you thank you.
Was really helpful. Especially calculating the final time
Thank you (nearly 12 years later!). Very clear and helped me solve a problem.
So thankful for your video. I was so frustrated with the time sheet I had created and it was such a tiny error. I didn't space between my time and AM/PM. Learning how to format the cells correctly was exciting too! You explained it so well! Thank you!
Thank you Vicki. I am pleased that I could help you to understand how to enter and calculate Time Values in Excel. As you learned, Excel requires a strict syntax when entering Time Values!
I appreciate your feedback.
Danny Rocks
The Company Rocks
Your video solved my problem just in a minute. Thank you.
Finally I have an answer that has befuddled me to no end! Thank You for making it as plan as MUD.
Worked perfect for me!!! Great instruction!!!
oh my goodness THANK YOU!!! frustration #3 was my issue and now solved!!
Absolutely brilliant. Thank you very much. You saved me a lot of time.
Thank you Danny! You ended my frustration today with the MOD function:))).
You made this concept so easy to follow. Thank you! :-)
From your explanation I was able to resolve the total sum of time in any array. My frustration while creating a Timesheet was; excel tends to round-off values over the 24hr clock into 1 Day rather than returning a value greater than 24:01:00 for hourly billing.
Thanks for this -it was helpful and straight to the point!
Thanks. the hint on using the custom format fixed my problems with trying to calculate years. I was using the date format on the column for subtracting two dates and that was showing 1904 as the year with is correct if your counting the number of days between start date and end date and you start at the year 1900.
These functions saved my life! Thank you for your great tutorial!
Thank you very much, finally for the the toturial issue number one
Thanks so much Danny! Your rock! (Excuse the pun). You just saved me hours of wasting time trying to solve this with the Excel help files. Your video is excellent and so helpful. I wish you much success and prosperity for the future.
Thank you so much for this tutorial, I was beginning to get frustrated trying to calculate time until I found this video. Once again thank you so much it saved me lot of time and lots of energy.
@pjdeaner
Thank you. I am pleased that you enjoyed my video tutorial.
Danny Rocks
The Company Rocks
Thank you very much for your kind words. I am pleased that you enjoyed my video tutorial.
Danny Rocks
The Company Rocks
Danny, pardon the cliché, but you do indeed rock! 3 hours solved in 5 minutes.
Wonderful, it is indeed a great video. It would be great to have the Excel file shown in the video, it will help me to practice. Can you please give the link to download the excel file... Appreciate your kind understanding. Sanjeeb
Quick, straightforward, simple! Thanks!
@MrPrios1
Thanks! I am pleased that I could help you.
Here is a quick thougt for your Date / Time question: 1) Enter =NOW() into a cell to get the current date and time in 1 cell. 2) Copy this and Paste Special Values to turn it into a constant (this is an easy way to ensure that you have the correct format for date and time) 3) Edit the Date and the Time in the Cell or in the Formula Bar to get your Starting Date / Time. From there you can "increment" the date & Time as you wish.
Danny
Very helpful tips. Thanks for sharing!
@johnmaxvan
Great! I am pleased that I could help you with this. Calculating Time differences in Excel can be frustrating - the details really count.
Danny Rocks
The Company Rocks
Hello Danny, thanks for this great tutorial. Do you have a follow on tutorial that shows the formula for taking out the lunch portion of the day? Thanks and LMK when you can.
thanks danny rocks! i had to review why my problem at work kept on happening in excel. good thing i found your vid! =)
thank you sir, you really helped me solve a major bug in the MIS with this 3rd level of frustration solving solution.
Thank you sir this video has been a great help in Graveyard Schedule.
I was going crazy trying to sum a list of times. [h]:mm saved me, so simple but not obvious. Excellent video with the visuals and good explanation. Thank you!
Thanks for the upload. You have answered my questions
Hey, Really wanna thank you for the effort you put here, you are the KING of Excel. There is any video about time calculation in 24h-format?
Thanks in advanced
Thanks a lot sir. Can you please give me an idea how to freeze the time format in a cell so that it only accept the value in time format only
Thank you! I am very pleased that my tutorial helped you a lot!
Danny Rocks
The Company Rocks
Hi Danny, thanks for the Time tutorial superb. Is there a simple solution for adding pound and ounces ? Kind regards Paul
Great informative video. Could you find the time to do an index and match video?
@Pitahchez
Yes, in order to display the total amount of hours worked, you MUST apply a Custom Format to the cells with the Total Hours worked - Use [h]:mm
This is the biggest challenge when working with Time Calculations.
For Custom Formatting, Right-mouse-click the cell - choose Format Cells. On the Number Tab, Choose Custom from the Category and then type in [h]:mm
Danny Rocks
The Company Rocks
My pleasure! Understanding how to correctly calculate Time Intervals in Excel can cause much frustration,
I am glad that I could help you.
Danny Rocks
The Company Rocks
You're really good at teaching
Danny
Thanks your instruction was vary easy to follow and that Mod() function is fantastic.
I knew as soon as I heard you speak that you must do this professionally. You do really Rock thanks again.
Robert
Thank you very much for this video! I was able fix several problem with my pick schedule thanks to you.
Hello Charles -
I apologize for the delay in responding. I am pleased that my Excel Tutorial helped you with your problem.
Danny Rocks
The Company Rocks
Hi Anthony - I tried to recreate your question and here is what I suggest:
1) Enter your values using this syntax: 00:00:30 for Thirty Seconds.
2) Notice that when you enter 00:00:60 for 60 seconds, Excel formats it as 00:01:00 (1 minute)
3) If you do not want to see the Hour Component, simply format the cells using the custom mm:ss mask.
Excel is very rigid when it comes to accepting "Time Value" entries. Use the hh:mm:ss syntax for entering seconds.
Danny Rocks
The Company Rocks
Easy to understand, great.
Thank you, extremely helpful!You are a great teacher
oh my goodness! best help! I'm still having some issues with the elapsed time. Say I start something at 8am only want to spend 30 minutes but go over by 15 - how would I add or subtract the time with that function. Great help! Thank you so much!
omg exactly what i needed. You are an angel
@InnerOrchestra
Hi -
Thanks for posting your comment. I have to admit that I have not yet watched "The Matrix" movie, so I will have to rent it.
I am pleased that I could make this a "fun" video. Learning should be fun in my opinion.
Sincerely,
Danny Rocks
The Company Rocks
Thank you very much, this was very helpful!
Gracias!!! Your video is easy to follow and solved all my questions :)
Excellent video. I encountered error # 3 (calculation crosses midnight), and it was easily fixed with your instructions. Thank you.
I checked this video three times now and fixed most of my problems with those simple tips - thank you! I have another problem: I have a date and I want to calculate the number of hours due on that date, so it would be something like if +7 then add 30:24 but I cannot figure out how to do it. Each week adds another 30:24 due. One (30:24) is always at the same place, the other one is moving down the column. Can someone help me? Nicole from Canada
To calculate the amount of time that has elapsed, use this formula: Ending Time - Starting Time. So, in your example, 7:00 PM (Ending Time) - 3:45 PM (Starting Time) is 3:15 - Three Hours and 15 minutes. Be sure to format the cell for your result as "h:mm"
There you go!
Danny Rocks
The Company Rocks
arase🐓maxx🦷
My pleasure! I am happy that you enjoyed my tutorial.
Thaks for adding your comment.
Danny Rocks
The Company Rocks
Yes sir i am trying to calculate straight time from total hours how do i do this please my total hours is 7.50 how do i get straight time please thank you
hehe
Nice to find a video that delivers five minutes' worth of content in five minutes as opposed to fifteen. Thanks! Also, you're still intelligible at 2x :)
I would advise there are 5 areas of frustration. You actually touched on some issues that bothered me long ago. The 5th area of issue would be time conversion like MT to ET or vice versa; this goes hand in hand when you were using "flights" as an example. Adding 2 hours: =A1+"02:00" if you had 22:00 in A1, the formula would show 00:00. But if you needed to go from ET to MT, the formula would be =24+A1-"02:00" 24, represents 24 hours in a day, if you put 00:00 in A1 it would return 22:00.
Thanks mate, I spend like 2 hours trying to find solution for my issue and your video saved me from suicide
So extremely helpful. YOU ROCK!
I love you! And I don't even know you. Thank you very much!