How to Solve 4 Frustrating Time Calculations in MS Excel 200

[@smk20136]

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

[@famiraracy]

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!

[@lukebrown4184]

You are literally the only person on the face of the earth that can explain this clearly. Thank you!

[@zipperdeedooha]

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.

[@KuldeepKumar-pj1gf]

THANKS FOR TIP. Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks

[@villarojonuwel]

OMG. I've been searching this for almost 3 months. Now I've found it! Thank you so much!!!

[@kkondrad]

This was a great tutorial. It helped me to quickly resolve my time calculations. I was very frustrated prior. Thank you!

[@berryj.greene7090]

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.

[@latinazn007]

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!

[@PrasannjeetSingh]

After 12 years of uploading this video... It still helped me :) Thanks a ton!

[@Lifesabre444]

I love the tutorial. It's right to the point and you make it easier to understand than most tutorials. Thank you!

[@srimz]

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

[@jaxsargent7075]

Thank you. The simplest mistakes can cause the biggest problems. Thank you for this quick tutorial.

[@johnhansenglishforgermans6166]

Thank you for very clear instructions that are easy to understand and follow. I am now going to look for more of your videos.

[@Respectfully888]

You are a life saver, watched some of your videos already and spot on fix to my excel nightmares. THANK YOU.

[@djjammindave]

DANNY ROCKS !!!!! THANK YOU.....THOSE BRACKETS MAKE A LOT OF DIFFERENCE...PROBLEM SOLVED.

[@MrJohnHimself]

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.

[@thirstydhampir]

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

[@DannyRocksExcels]

De nada! My pleasure. I am delighted that I could help you. Thank you for adding your comments. Danny Rocks The Company Rocks

[@bhavyadoshi26]

This is the best explanation for time calculation problems in Excel. Thanks a ton for this great video!

[@excelworx8712]

What a time saver! Short and to the point!

[@christoskylar2601]

Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks Danny!!!

[@oliviermanbode4788]

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

[@NSswayamPrakash]

Thank you for the Time Formulas, it really works in doing the calculations of adding worked hours of a week....Marvelous Mr.Rocks

[@davidw1324]

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.

[@sesvvls1]

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!

[@MuhammadAli-ij9xj]

Thank you Sir , I was looking the minus two time between different days around. It is very easy and help full. Thanks

[@mariateresavillaflor8264]

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.

[@kimberlybadanich720]

oh my goodness THANK YOU!!! frustration #3 was my issue and now solved!!

[@chancefinley2432]

Loved this. Great tutorial. I was having frustrations with times crossing midnight. Saved me a bunch of time!

[@MotherWatts]

Thank you (nearly 12 years later!). Very clear and helped me solve a problem.

[@lisalynne2330]

OH MY GOD!!!! i have been trying to find the formula for adding total hours. Thank you!!!!!! I love you.

[@samisayque]

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.

[@rohanck]

Thank you for this video! You've clarified matters in such a simple way.

[@ginotacobell3098]

Excellent teacher. Your videos are so clear and easy to follow

[@TP014563]

straight to the point, no lengthy introductions!! thanks a lot

[@azharuddinshakeel1134]

TP014563

[@nandan.nathjan2020]

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"

[@lazalazarevic6192]

Thanks for the tip. One of the most important things in Excel: FORMATING FORMATING FORMATING (Ctrl+1)

[@munna7084]

You are amazing

[@assie169]

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.

[@MCMaimz]

Danny, you rock alright! I cannot believe how convoluted the other youtube tutorials are compared to yours. Thanks!! Steve

[@jeaninesciacca7793]

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.

[@UbuntuXII]

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

[@Nagaraj0605]

Your video solved my problem just in a minute. Thank you.

[@etslowe1528]

Thank you so much. I was so frustrated until I watched your video!

[@WiliamBennettwildarbennett]

Finally I have an answer that has befuddled me to no end! Thank You for making it as plan as MUD.

[@palobar9974]

Thank you Danny! You ended my frustration today with the MOD function:))).

[@omenassessors4927]

thank you sir, you really helped me solve a major bug in the MIS with this 3rd level of frustration solving solution.

[@johnblackstock4092]

Thank you, half way through I got the answer I needed. Cheers

[@stephanyosei3113]

Was really helpful. Especially calculating the final time

[@wanderingwolf78729]

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!

[@starsumit2367]

Whenever I need help I am typing to Danny rocks from last 5 years n see still I am getting finest videos

[@DannyRocksExcels]

@pjdeaner Thank you. I am pleased that you enjoyed my video tutorial. Danny Rocks The Company Rocks

[@DannyRocksExcels]

Thank you very much for your kind words. I am pleased that you enjoyed my video tutorial. Danny Rocks The Company Rocks

[@Pitahchez]

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.

[@DannyRocksExcels]

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

[@johnmaxvan]

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

[@michaelnguyen6891]

You're really good at teaching

[@DannyRocksExcels]

@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

[@shahboy68]

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.

[@connect_sphere]

These functions saved my life! Thank you for your great tutorial!

[@Brokendiode]

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

[@DannyRocksExcels]

@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

[@anamata461]

Thank you very much, finally for the the toturial issue number one

[@MariaLigaya09]

thanks danny rocks! i had to review why my problem at work kept on happening in excel. good thing i found your vid! =)

[@donnpoff]

This was INCREDIBLY helpful!

[@silvertourist]

Hi Danny, thanks for the Time tutorial superb. Is there a simple solution for adding pound and ounces ? Kind regards Paul

[@ericerickson4876]

Great informative video. Could you find the time to do an index and match video?

[@DannyRocksExcels]

My pleasure! I am happy that you enjoyed my tutorial. Thaks for adding your comment. Danny Rocks The Company Rocks

[@aliciamolina3783]

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

[@서단추-f1j]

hehe

[@RisNanDigital]

Hello this is such a life saver!. Thank you for sharing your knowledge God Bless and more power!

[@saidimon]

This 12 years-old vídeo is a legendary to me! It saved-me right now as well!!! =D

[@MrRegulator31]

Worked perfect for me!!! Great instruction!!!

[@atulthakurinbox]

I wonder, who are these 9 dislikers? Such an important information he is giving. Thanks a lot.

[@pklubdub]

Excellent video. I encountered error # 3 (calculation crosses midnight), and it was easily fixed with your instructions. Thank you.

[@brianworthington2050]

Absolutely brilliant. Thank you very much. You saved me a lot of time.

[@DannyRocksExcels]

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]

arase🐓maxx🦷

[@DannyRocksExcels]

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

[@lindahuffaker1450]

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.

[@SunnyDCurlesrainbow545]

The space before the AM or PM is what threw me off! Thank!

[@libbyd7842]

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.

[@thepeddler9226]

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

[@MrBat000]

Thanks for the upload. You have answered my questions

[@DannyRocksExcels]

@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

[@sulja786]

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

[@soEndsLost]

Thank you very much for this video! I was able fix several problem with my pick schedule thanks to you.

[@DannyRocksExcels]

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

[@LORDMEKAZ]

Thanks mate, I spend like 2 hours trying to find solution for my issue and your video saved me from suicide

[@MrPrios1]

Thanks that was great. It solved two of my issues. One question, is there a way to associate a cell with a date and specific time? For example, I want a cell to show a date and the time to be 14:00. Then the next cells would calculate from that date and time. Pilo

[@TheTEAR1]

Thanks so much,, this really help,, i was near to frustration

[@DannyRocksExcels]

@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

[@deannastrachan449]

Danny, pardon the cliché, but you do indeed rock! 3 hours solved in 5 minutes.

[@DannyRocksExcels]

@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

[@melanierice8588]

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!

[@marcelamartinez8409]

Easy to understand, great.

[@omer4m1]

Thank you sir this video has been a great help in Graveyard Schedule.

[@abdulsamiruknuddin3364]

Danny u answered my frustration in no time👍🏻

[@mariboni516]

You made this concept so easy to follow. Thank you! :-)

[@gouritanurath783]

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

[@tcat5933]

Danny, good morning. Any thoughts on a formula that would subtract unpaid lunches with the following provisions: lunch will always be a mandatory 30 minutes minimum or actual time punched out and in for lunch?

[@jezwc]

Thanks for this -it was helpful and straight to the point!

[@nitinsingh.2107]

Thanx, esp for calculating between the date change

[@aaroncarr4245]

you do rock lol. I'm new to excel and this is super helpful thanks

[@DannyRocksExcels]

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