How to Solve 4 Frustrating Time Calculations in MS Excel 200
Тәжірибелік нұсқаулар және стиль
My clients have the most difficulty performing Excel "time value" calculations. How about you?
One tip - learn the proper format for a the cell that contains your "time value" calculation.
Watch this short video as I demonstrate each of the 4 frustrations my clients face when calculating "time values" in MS Excel.
I invite you to visit my website -
www.thecompanyrocks.com/excels -
to view all of my Excel Video Lessons.
Пікірлер: 306
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!
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 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!! 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 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.
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!
I love the tutorial. It's right to the point and you make it easier to understand than most tutorials. Thank you!
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.
You are literally the only person on the face of the earth that can explain this clearly. Thank you!
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 for the tip. One of the most important things in Excel: FORMATING FORMATING FORMATING (Ctrl+1)
@munna7084
3 жыл бұрын
You are amazing
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 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
Loved this. Great tutorial. I was having frustrations with times crossing midnight. Saved me a bunch of time!
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 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
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!!!
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 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
straight to the point, no lengthy introductions!! thanks a lot
@azharuddinshakeel1134
6 жыл бұрын
TP014563
This is the best explanation for time calculation problems in Excel. Thanks a ton for this great video!
Thank you very much for your kind words. I am pleased that you enjoyed my video tutorial. Danny Rocks The Company Rocks
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.
OH MY GOD!!!! i have been trying to find the formula for adding total hours. Thank you!!!!!! I love you.
thank you sir, you really helped me solve a major bug in the MIS with this 3rd level of frustration solving solution.
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
This was a great tutorial. It helped me to quickly resolve my time calculations. I was very frustrated prior. Thank you!
THANKS FOR TIP. Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks
What a time saver! Short and to the point!
@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
Thank you. The simplest mistakes can cause the biggest problems. Thank you for this quick tutorial.
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..
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.
Danny, pardon the cliché, but you do indeed rock! 3 hours solved in 5 minutes.
Thank you Sir , I was looking the minus two time between different days around. It is very easy and help full. Thanks
Quick, straightforward, simple! Thanks!
Finally I have an answer that has befuddled me to no end! Thank You for making it as plan as MUD.
just what i have been looking for ! will help to make my travellar schedule (textile rings replacement ) and machines stoppage time calculation !!!! thanks once more !!!!!
Thank you very much for this video! I was able fix several problem with my pick schedule thanks to you.
@DannyRocksExcels
10 жыл бұрын
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
OMG. I've been searching this for almost 3 months. Now I've found it! Thank you so much!!!
After 12 years of uploading this video... It still helped me :) Thanks a ton!
@pjdeaner Thank you. I am pleased that you enjoyed my video tutorial. Danny Rocks The Company Rocks
DANNY ROCKS !!!!! THANK YOU.....THOSE BRACKETS MAKE A LOT OF DIFFERENCE...PROBLEM SOLVED.
This was INCREDIBLY helpful!
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 for very clear instructions that are easy to understand and follow. I am now going to look for more of your videos.
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!
Danny, you rock alright! I cannot believe how convoluted the other youtube tutorials are compared to yours. Thanks!! Steve
I am trying to work with time in excell for over a week and there is nothing I can do to make it calculate time diferences in order to get in the result , the time that employees spend inside a room. We have badge IN and OUT system, so I can pull a report to show me the time that a specific employee entered or exited through a door. When I export the report to excell it gives me H:M:S(PM/AM) without space. In a separeted cell I enter ex.. =B1-C1 and all I get is #VALUE.. No matter what cell configuration I use.. I think that time are data that we use so often , I am not sure why microsoft complicated it so much
Absolutely brilliant. Thank you very much. You saved me a lot of time.
Was really helpful. Especially calculating the final time
Excellent teacher. Your videos are so clear and easy to follow
Thank you (nearly 12 years later!). Very clear and helped me solve a problem.
oh my goodness THANK YOU!!! frustration #3 was my issue and now solved!!
@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, extremely helpful!You are a great teacher
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 very much for the tips. You do it in a smooth and perfect way. But I have one more question. I have a huge list in which I want to calculate time between two columns and in some cases the ending hour is after midnight and in other cases it is not (and this changes day by day). What should I do ? Basicaly I want to combine frastration nr 2 and 3 but each one works in different cases.
@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
Thanks for the upload. You have answered my questions
My pleasure! I am happy that you enjoyed my tutorial. Thaks for adding your comment. Danny Rocks The Company Rocks
@aliciamolina3783
5 жыл бұрын
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
@user-ls7zx7tc2u
4 жыл бұрын
hehe
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.
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.
Thank you, half way through I got the answer I needed. Cheers
Your video solved my problem just in a minute. Thank you.
Thanks for this -it was helpful and straight to the point!
I wonder, who are these 9 dislikers? Such an important information he is giving. Thanks a lot.
Excellent video. I encountered error # 3 (calculation crosses midnight), and it was easily fixed with your instructions. Thank you.
So extremely helpful. YOU ROCK!
These functions saved my life! Thank you for your great tutorial!
Whenever I need help I am typing to Danny rocks from last 5 years n see still I am getting finest videos
Genius tutorial, thank you!
Worked perfect for me!!! Great instruction!!!
Thanks mate, I spend like 2 hours trying to find solution for my issue and your video saved me from suicide
Easy to understand, great.
thanks danny rocks! i had to review why my problem at work kept on happening in excel. good thing i found your vid! =)
Very helpful tips. Thanks for sharing!
Thank you very much, this was very helpful!
Thanx, esp for calculating between the date change
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.
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.
Thank you very much for the tutorial.
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
@adhyayanghosh6416
2 жыл бұрын
arase🐓maxx🦷
Thank you Danny! You ended my frustration today with the MOD function:))).
My please. Glad that you enjoyed my video tutorial. Danny Rocks The Company Rocks
Thank you Mr. Danny
You made this concept so easy to follow. Thank you! :-)
Thanks for your video. I figured out my issue, Your video is great!
omg exactly what i needed. You are an angel
Thank you very much, was very helpful
Thank you very much, finally for the the toturial issue number one
Such a lifesaver. Thank you a lot.
Hi Danny, thanks for the Time tutorial superb. Is there a simple solution for adding pound and ounces ? Kind regards Paul
Thank you so much for this!!!!
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
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.
You're really good at teaching