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.
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
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! :-)
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!
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..
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.
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.
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 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
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!
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.
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.
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.
This video rocks. I was just about to give up, after 5 hours of failed searches that did not address calculating time over 24 hours. Excel 2010 kept giving me 7 hours instead of 31.
@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
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
@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
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
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
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
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
@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
@agbluvsu2 Glad that you enjoyed the video! Re: Graveyard Shift - aka, Time that Crosses Midnight Simple answer - use the MOD() Function for this: =MOD((EndTime - StartTime),1) then, remember to format the Formula Cell as [h]:mm to get the total number of hours worked. So, without accounting for Meal Breaks, the formula would be: =MOD((6:00 AM - 10:00 PM),1) - Obviously, you would use cell references rather than these values. Pay attention to the pairs of (). Danny 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
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 :)
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
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!
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
My Pleasure! Yes, the =MOD() Function is a "life saver" for anyone calculating time that crosses "midnight." - Flight schedules. Graveyard Schedule Workers, etc. It is so much easier than writing "Nested IF()" Functions! Danny Rocks The Company Rocks
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!
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.
You are literally the only person on the face of the earth that can explain this clearly. Thank you!
THANKS FOR TIP.
Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks
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
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!
Thank you. The simplest mistakes can cause the biggest problems. Thank you for this quick tutorial.
I love the tutorial. It's right to the point and you make it easier to understand than most tutorials. Thank you!
OMG. I've been searching this for almost 3 months. Now I've found it! Thank you so much!!!
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! :-)
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!
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..
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.
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 very clear instructions that are easy to understand and follow. I am now going to look for more of your videos.
After 12 years of uploading this video... It still helped me :) Thanks a ton!
You are a life saver, watched some of your videos already and spot on fix to my excel nightmares. THANK YOU.
DANNY ROCKS !!!!! THANK YOU.....THOSE BRACKETS MAKE A LOT OF DIFFERENCE...PROBLEM SOLVED.
Thank you Sir , I was looking the minus two time between different days around. It is very easy and help full. Thanks
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.
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.
Thank you for this video! You've clarified matters in such a simple way.
Thank you for the Time Formulas, it really works in doing the calculations of adding worked hours of a week....Marvelous Mr.Rocks
Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks Danny!!!
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"
De nada! My pleasure. I am delighted that I could help you.
Thank you for adding your comments.
Danny Rocks
The Company Rocks
This is the best explanation for time calculation problems in Excel. Thanks a ton for this great video!
Thanks for the tip.
One of the most important things in Excel: FORMATING FORMATING FORMATING (Ctrl+1)
You are amazing
What a time saver! Short and to the point!
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.
oh my goodness THANK YOU!!! frustration #3 was my issue and now solved!!
Thank you (nearly 12 years later!). Very clear and helped me solve a problem.
Loved this. Great tutorial. I was having frustrations with times crossing midnight. Saved me a bunch of time!
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
Excellent teacher. Your videos are so clear and easy to follow
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
thank you sir, you really helped me solve a major bug in the MIS with this 3rd level of frustration solving solution.
straight to the point, no lengthy introductions!! thanks a lot
TP014563
Was really helpful. Especially calculating the final time
OH MY GOD!!!! i have been trying to find the formula for adding total hours. Thank you!!!!!! I love you.
Danny, you rock alright! I cannot believe how convoluted the other youtube tutorials are compared to yours. Thanks!! Steve
Thank you Danny! You ended my frustration today with the MOD function:))).
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!
Your video solved my problem just in a minute. Thank you.
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.
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.
Finally I have an answer that has befuddled me to no end! Thank You for making it as plan as MUD.
Excellent video. I encountered error # 3 (calculation crosses midnight), and it was easily fixed with your instructions. Thank you.
Thank you, half way through I got the answer I needed. Cheers
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.
Whenever I need help I am typing to Danny rocks from last 5 years n see still I am getting finest videos
thanks danny rocks! i had to review why my problem at work kept on happening in excel. good thing i found your vid! =)
I wonder, who are these 9 dislikers?
Such an important information he is giving. Thanks a lot.
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.
Thank you so much. I was so frustrated until I watched your video!
This video rocks. I was just about to give up, after 5 hours of failed searches that did not address calculating time over 24 hours. Excel 2010 kept giving me 7 hours instead of 31.
These functions saved my life! Thank you for your great tutorial!
Thanks mate, I spend like 2 hours trying to find solution for my issue and your video saved me from suicide
@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
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
@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
@pjdeaner
Thank you. I am pleased that you enjoyed my video tutorial.
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
Thank you very much for your kind words. I am pleased that you enjoyed my video tutorial.
Danny Rocks
The Company Rocks
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
Danny, pardon the cliché, but you do indeed rock! 3 hours solved in 5 minutes.
This was INCREDIBLY helpful!
The space before the AM or PM is what threw me off! Thank!
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
Absolutely brilliant. Thank you very much. You saved me a lot of time.
THANX DANNY
it really helped
i got the formulae on the net but missing part was the space in between am/pm and time
DANNY ROCKS
Thank you sir this video has been a great help in Graveyard Schedule.
Thanks for the upload. You have answered my questions
Thank you very much, finally for the the toturial issue number one
You made this concept so easy to follow. Thank you! :-)
Thanks for this -it was helpful and straight to the point!
Danny u answered my frustration in no time👍🏻
Worked perfect for me!!! Great instruction!!!
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
@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
@agbluvsu2
Glad that you enjoyed the video!
Re: Graveyard Shift - aka, Time that Crosses Midnight
Simple answer - use the MOD() Function for this:
=MOD((EndTime - StartTime),1) then, remember to format the Formula Cell as [h]:mm to get the total number of hours worked.
So, without accounting for Meal Breaks, the formula would be:
=MOD((6:00 AM - 10:00 PM),1) - Obviously, you would use cell references rather than these values. Pay attention to the pairs of ().
Danny 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
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 :)
You're really good at teaching
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🦷
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!
Quick, straightforward, simple! Thanks!
It's 12 year to upload this video, but still it's helping us 👍
Very helpful tips. Thanks for sharing!
Thanks so much,, this really help,, i was near to frustration
you do rock lol. I'm new to excel and this is super helpful thanks
Thank you very much, this was very helpful!
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
Thanx, esp for calculating between the date change
Thanks very much, the mod function solved my midnight crossover problem exactly
My Pleasure!
Yes, the =MOD() Function is a "life saver" for anyone calculating time that crosses "midnight." - Flight schedules. Graveyard Schedule Workers, etc.
It is so much easier than writing "Nested IF()" Functions!
Danny Rocks
The Company Rocks
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.
Thank you! I am very pleased that my tutorial helped you a lot!
Danny Rocks
The Company Rocks
Thanks for your video. I figured out my issue, Your video is great!