How to Solve 4 Frustrating Time Calculations in MS Excel 200
ฝัง
- เผยแพร่เมื่อ 30 ก.ย. 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
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
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.
I love the tutorial. It's right to the point and you make it easier to understand than most tutorials. Thank you!
I love you! And I don't even know you. Thank you very much!
Thanks for the tip.
One of the most important things in Excel: FORMATING FORMATING FORMATING (Ctrl+1)
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.
This was a great tutorial. It helped me to quickly resolve my time calculations. I was very frustrated prior. Thank you!
Thank you for this video! You've clarified matters in such a simple way.
I wonder, who are these 9 dislikers?
Such an important information he is giving. Thanks a lot.
Could you tell me how to covert total hours to decimals in Microsoft Excel... for example, Column A = 1:00PM, Column B = 3:15PM... how can I have Column C (TOTAL HOURS) read "2.25"
all times at my job are in 15 minute increments
1:15PM to 2:00PM would be .75
5:30PM to to 7:00PM would be 1.5
any help is much appreciated !!!
I am trying to calculate the variance between the scheduled end time and actual end time. I am looking for a formula that calculates overnight (am-pm), am - am, and pm -pm. The formula I am using is the mod formula which works perfectly until it attempts to calculate am - am. Then it gives me 23 hours. Example: Scheduled to clock out at 5:30 AM, and the actual punch out is 5:29 AM= 23:59. Here is my formula =IFERROR(MOD(TIMEVALUE($W8)-TIMEVALUE($O8),1),"") What formula can I use to calculate everything?
how to use a function: IF(K1
How to sum values, based on time of day?
Example:
$20 at 12.45
$30 at 16.20
$40 at 08.45
$10 at 12.10
And i need to sum of $ for each hour, so 12:00-13:00 gives $30. How to do that?
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"
What I can't find anywhere is how to format cell to input DURATION and not hour of the day. Say there's a list of thing to do that take X hours and Minutes (sometime more than 24 hours). I have tried everything, but Excel keeps turning it to AM/PM entries. I would be shocked if Excel is not capable of simple duration entries....
Anthony - Here is how you solve this:
1) There are 86,400 seconds in a 24 hour day.
2) Take the number of seconds (380) and divide by 86,400 (=380/86400)
3) The formatting for your result will look odd. So, format this cell using the Custom Format [h]:mm:ss
Danny Rocks
The Company Rocks
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 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.
Thanks for the guide. Yet I still have a problem which has proved surprisingly difficult to resolve in all of my Google searches. It's beginning to seem that no one has ever tried to do this simple thing and its solution would be worthy of the Fields Medal for Excel or the Nobel Prize! Ready? Count the number of hours between two times and have the result as a number (not a time) that can be summed and totalled. That's it. Yet there seems to be no search in all of Excel, no search in all of Google, and no search in the internet that an answer this question. The nearest I've ever come is he TEXT function which does it, but resolves the answer in a text that cannot be calculated on. If you're able to resolve this I will award you the Millennium Prize. XD.
De nada! My pleasure. I am delighted that I could help you.
Thank you for adding your comments.
Danny Rocks
The Company Rocks
Sir, please teach me to change AM to PM time. I don't want to do and change it manually. So frustrating when you're not an expert with EXCEL 😣😣😣
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?
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🦷
I want millisecond format and I don't have hh:mm:ss.000 in my excel 2013 format window. Is there any add-in for that? pls help.
oh my goodness THANK YOU!!! frustration #3 was my issue and now solved!!
@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
Hi Danny.. If the both times in same meridian and need to calculate the time between the two times such as first one is 7:44AM and second one is 6:21AM. How it could be calculate from bigger one to smaller one.
You are a legend man, but I have one more frustration. If I multiply the cell with the "mod" function with a currency, I don't get the right answer.
Forgot to say, the bar code will not have the time in it. Bar code will only have one number per bar code from 1 to 6.
excuse me, I want ask
If i have Break time every Day for Lunch and break time not Included, How to Create Formula
@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
Thank you Sir , I was looking the minus two time between different days around. It is very easy and help full. Thanks
Thank you Danny! You ended my frustration today with the MOD function:))).
Very helpful, but I tried that [h]:mm format and Excel still interprets the numbers as a time of day instead of a duration.
You are literally the only person on the face of the earth that can explain this clearly. Thank you!
I couldn't do jack! so frustrating I'm just doing a simple time sheet 7:47 to 2:30pm! I used my fingers!!
searching for solutions, importing timesheet attendence into c# sqlserver payroll software and the time format has become show stopper rather pooper!
I can't get past stage No 1. My cells do not add up time, they are adding as units of 10. Is there something that I have to do with the sheet before I start entering data
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
HOW ABOUT THIS EXAMPLE :- CELL A (9:00AM - 1:00PM) CELL B
(5:00PM - 10:00PM) HOW TO CALCULATE
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!
What if you have several cells making elapsed time calculations, but sometimes one of the cells (arrival or departure) is empty? I get ####### and ISERROR doesn't help with that.
ISERROR Doesn't work because it is not an error, it is a serial number less than 0. Use (Calculation)
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
Thanks mate, I spend like 2 hours trying to find solution for my issue and your video saved me from suicide
Good day sir! I really need your help in fixing this problem. How can you get STANDARD DEVIATION AND MEAN if you have a time with minutes,seconds and milliseconds (ex. 0:1.66, 0:1.50,0:1.10) the point (.) after the number 1 is in milliseconds. What is the formula in getting the answer? Thanks! :)
hi can you please get me if en employee get 20 rupees for time punch between 7:45 am to 8:15am
@marwan111986
I am pleased that I could help you. Thank you for writing to tell me this.
Danny Rocks
The Company Rocks
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! :-)
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
Can we create graphic base on time (hh:mm:ss) ? Frustated no. 5
Thanks for the upload. You have answered my questions
Hi.
Can you teach me minus farmula between hours and minutes?
How can we minus the 12345:50 from 11340:30
It works upto 9999:50 hrs.
But
As soon as it cross it. It gives VALUE# IN the answer column.
@pjdeaner
Thank you. I am pleased that you enjoyed my video tutorial.
Danny Rocks
The Company Rocks
Loved this. Great tutorial. I was having frustrations with times crossing midnight. Saved me a bunch of time!
I want to calculate the total hours a machine has operated using both date and time start/finish3-21-18 2:20 pm 3-22-18 4:50 am. If possible I would like to calculate the rate/hour with down time.Machine runs 40,000 pounds in 20 hours with 0 downtime the rate would be 2000 lbs/hr. What is rate with 3 hours downtime. How do I calculate the rate with 3 hours down time (1,600/hr, 25 hr run time)?
Suddenly at 2:44 you say "let's call up the function arguments", but how on earth do you do that?
shift + f3
Or just enter the function name and an opening bracket and then hit the little fx.
Thank you saved me time and money (would've broken the keyboard).
THIS FORMULAE ALSO HELPS= B1-A1+IF(A1>B1,1)
FOR EXAMPLE=
=6:05 AM-10:54 PM+IF(10:54 PM>6:05 AM,1)
=7:11
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
Hi, thank you for your video. One question, do you know a code to make excel recognize the hour a bar code was scanned (that would be the start time) then scan another bar code (end time) and it automatically calculates the difference in time? Thank you very much.
Thank you very much, this was very helpful!
you do rock lol. I'm new to excel and this is super helpful thanks
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.
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.
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..
Very Helpful video. I do have a question though... What about in the case of the following: I have a spreadsheet where the user enters the start time and a estimated end time will populate. At the end of the operation the user will enter the Actual End Time... I am having difficult time populating a difference calculation between the estimated end time and the actual. I'm looking for a formula that can populate the difference in time between finishing on time or late.
The examples below are actual example from my spread sheet...
Example 1
Start Time - Estimated End Time - Actual End Time - Difference
7:10 AM 10:41 AM 9:28 AM 22:46
My existing formula is =MOD(Actual End Time - Estimated End Time,1)
When I change it to =MOD(ABS(Actual End Time - Estimated End Time),1) Then it gives me the correct output of 1:13.
Example 2
Start Time - Estimated End Time - Actual End Time - Difference
11:56 PM 3:54 AM 6:43 AM 21:11
My existing formula is =MOD(ABS(Actual End Time - Estimated End Time),1)
When I change the formula and take the ABS off then it gives me the correct output of 2:49.
I need one formula that can handle both scenarios.
Suggestions?
I'm years late asking, but how would I calculate the amount of wages paid based on hours and minutes worked? For example, 47hrs 30 mins would need to be 47.5 as 47.3 would actually be 47hrs and 18mins. I suppose that I could truncate and use an if statement to return .25, .5 and .75 to represent 15mins, half an hour, and three-quarters of an hour. Is there an easier way?
How do I figure the time difference of Pacific time vs Eastern time in real numbers....I have 8:30 PM and 5:30 PM...the result should be +3 because East Coast is 3 hours ahead....I also have 4:30 PM and 5:30 PM for a part of Alaska or the Pacific Islands....they should read -1 or -3....PLEASE HELP. I"m going crazy. How is this possible?!
Hello Sir, please could you let me that, what formula can I use to extract the first hour (from 6am until 7am) looking a different columns.
Example: I need to display on Cell C1 how many minutes has elapsed from 6am until 7am looking at columns A & B.
A B
06:00
06:30
07:00
07:12
08:12
08:59
very good video.... this is what i needed....
Thank you very much, finally for the the toturial issue number one
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.
How we add if we have lets say: (Hr:Min) but want sum in Hours?
A1: 12:00 =12hr, 0Min
A2: 08:00 =8hr, 0min
A3: 00:10 =0hr,10min
A4: 00:20 =0hr, 20min
Thanks,
Tek
Here's one for you.
A football game is live on TV. The elapsed time shows in the corner of the screen in minutes and seconds. If the game is stopped for amy number of reasons, the referee stops the clock and the time is frozen until play recommences.
So a player scores a goal whilst the clock is ticking and the TV clock shows 35 minutes and 15 seconds.
For the sake of the game statistics, what minute should be recorded for that score in the final breakdown?
Should it be 35 or 36 minutes?
Is there anywhere this sort of scenario is explained officially?
Danny, so far am doing ok. however am having an issue and don't know how to solve it. Here's my dilema. the objective is to calculate whether an employee's call should be paid an override or part of the call or if the call should be paid the regular rate. Any calls received before 9:00:00 should be paid an override rate of .40 while anything past 9:00:01 should be paid at regular rate (.25). if a call starts at 8:40:00 and ends at 9:30:00, then the employee's call should be paid a partial override rate and a partial at regular rate. when I use an if statement to compare the start time, it comes back with a false statement. (ie, 8:34:00 call started 8:58:00 call ended). in this scenario, when I compare the start time of 8:40:00 to the dead.line of 9:00:00, it comes back false. When I use the evaluate formula box in excel, it advises me is false. Why? Can you help. Thanks. I appreciate your videos.
Thank you sir this video has been a great help in Graveyard Schedule.
Thanks so much,, this really help,, i was near to frustration
It's 12 year to upload this video, but still it's helping us 👍
Adding the relevant Excel File would lend much more meaning.
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.
EXCELLENT VIDEO! Thank you for sharing your knowledge :D Straight to the point, covering several possible scenarios. #WellDone
Will it work on excel 2007. Because I did not see (h):mm, though i saw (h):mm:ss. When i tried to find the elapsed time by "function" subtracting from "arrival to departure" it comes like " value"
THANKS FOR TIP.
Finally I understand how Excel stores time (and dates). Thank you so much. You are a great teacher. Thanks
dear sir
please calculate the time Clock in time 10:36 & clock out 19:28 in excel the time shows that 8:52 the correct time is 9:32 so please sir how to calculate ??
OMG. I've been searching this for almost 3 months. Now I've found it! Thank you so much!!!
Thank you for your helpful video and the tips provided. I could make use of all 4 of them to resolve my issues, but I have 1 more tip that is relevant that you could perhaps comment on please?
In my timesheet scenario, the person will work a max. of 100 hours.
So when I enter a 100 as the CAP value, what formula must I use to now deduct the accrued hours of the timesheet, because I get all different kind of answers depending on various formatting I apply, but for none I can get it to just see that it is actually 100 hours that the other hours must be deducted from :-)
Yr formula is not working while = D4-C4 it shows " VALUE " many times I have tried no result.... pls advice
Greetings Sir,
Can you please provide with the solution to the below:
COUNTRIES TIME NOW CALLING TIME
India 21/07/2016, 6:40 PM ????
Orlando ??? 30/07/2016, 9:00 AM
1. How do i perform auto date/time for different countries ?
2. How do i calculate what time would it be in India if my given calling time is Orlando timing or vice-versa ?
3. Would goal seek be helpful & what will the best formula to be applied ?
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 :)
DANNY ROCKS !!!!! THANK YOU.....THOSE BRACKETS MAKE A LOT OF DIFFERENCE...PROBLEM SOLVED.
HI Danny can you not just tick the 1904 date system rule in options to do the same ?
Thank you, half way through I got the answer I needed. Cheers
thank you mr. danny.. so about 3:00 am minus 3:30 am at the same day or date??
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 !!!!!
Thanks for showing me how to subtract 10,54 PM from 6,05 AM (a duration that crosses midnight)
@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
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
I couldn't figure out how Danny got Excel to produce a single figure before the colon then two figures, for example, he has
7 : 11 where I was getting 07 : 11 - a zero BEFORE my desired figure. And then I figured it out.
At 1:46 when Danny explains the procedure for the 'Custom' option, he mentions using [h]:mm, however my Excel didn't have this, it only had [h]:mm:ss
I solved it by simply amending the [h]:mm:ss in the 'Type' box within the 'Custom' window. I did it by deleting the 'ss' then clicked 'OK'