How to Solve 4 Frustrating Time Calculations in MS Excel 200
HTML-код
- Опубликовано: 19 сен 2024
- 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.
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.
Thanks mate, I spend like 2 hours trying to find solution for my issue and your video saved me from suicide
THANKS FOR TIP.
Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks
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!
Whenever I need help I am typing to Danny rocks from last 5 years n see still I am getting finest videos
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.
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.
After 12 years of uploading this video... It still helped me :) Thanks a ton!
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"
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!
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
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! :-)
Thanks for the tip.
One of the most important things in Excel: FORMATING FORMATING FORMATING (Ctrl+1)
You are amazing
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
oh my goodness THANK YOU!!! frustration #3 was my issue and now solved!!
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.
Thank you (nearly 12 years later!). Very clear and helped me solve a problem.
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..
Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks Danny!!!
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!
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.
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 !!!!!
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 for the Time Formulas, it really works in doing the calculations of adding worked hours of a week....Marvelous Mr.Rocks
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 for very clear instructions that are easy to understand and follow. I am now going to look for more of your videos.
You are a life saver, watched some of your videos already and spot on fix to my excel nightmares. 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.
Was really helpful. Especially calculating the final time
Thank you for this video! You've clarified matters in such a simple way.
This is the best explanation for time calculation problems in Excel. Thanks a ton for this great video!
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.
straight to the point, no lengthy introductions!! thanks a lot
TP014563
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.
OH MY GOD!!!! i have been trying to find the formula for adding total hours. Thank you!!!!!! I love you.
@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 Danny! You ended my frustration today with the MOD function:))).
Thank you very much for your kind words. I am pleased that you enjoyed my video tutorial.
Danny Rocks
The Company Rocks
What a time saver! Short and to the point!
Loved this. Great tutorial. I was having frustrations with times crossing midnight. Saved me a bunch of time!
Excellent video. I encountered error # 3 (calculation crosses midnight), and it was easily fixed with your instructions. Thank you.
thank you sir, you really helped me solve a major bug in the MIS with this 3rd level of frustration solving solution.
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
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
@pjdeaner
Thank you. I am pleased that you enjoyed my video tutorial.
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
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.
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
Excellent teacher. Your videos are so clear and easy to follow
Thank you, half way through I got the answer I needed. Cheers
Danny, pardon the cliché, but you do indeed rock! 3 hours solved in 5 minutes.
Finally I have an answer that has befuddled me to no end! Thank You for making it as plan as MUD.
@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
I wonder, who are these 9 dislikers?
Such an important information he is giving. Thanks a lot.
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!
The space before the AM or PM is what threw me off! Thank!
Your video solved my problem just in a minute. Thank you.
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
@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
You're really good at teaching
Danny, you rock alright! I cannot believe how convoluted the other youtube tutorials are compared to yours. Thanks!! Steve
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. I was so frustrated until I watched your video!
It's 12 year to upload this video, but still it's helping us 👍
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! 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
These functions saved my life! Thank you for your great tutorial!
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 very much, finally for the the toturial issue number one
@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
@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
If 6:00 AM is the "Start Time" and 12:00 PM is the "End Time, use the formula =End Time - Start Time - that is = 12:00 PM - 6:00 AM.
The result of the Formula will show as a "Time" - e.g. 6:00 AM. You simply open the Format Cells Dialog box, choose Number - Custom and modify one of the samples to be [h]:mm to show the Hours: Minutes.
Danny Rocks
The Company Rocks
W
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 for the upload. You have answered my questions
thanks danny rocks! i had to review why my problem at work kept on happening in excel. good thing i found your vid! =)
Thanks so much,, this really help,, i was near to frustration
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
Thanx, esp for calculating between the date change
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
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 INCREDIBLY helpful!
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
Worked perfect for me!!! Great instruction!!!
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 :)
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!
Thank you sir this video has been a great help in Graveyard Schedule.
Quick, straightforward, simple! Thanks!
Easy to understand, great.
Absolutely brilliant. Thank you very much. You saved me a lot of time.
Thank you very much for the tutorial.
Danny u answered my frustration in no time👍🏻
Adding the relevant Excel File would lend much more meaning.
Thank you very much, this was very helpful!
Great.. Solved the issue.. Thanks for the info