 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2007Another Time Calulation Question - Sorry.
Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00  SignatureI don't mind being wrong, I'd just like you to prove it! :) Instead of the way you are doing it, remember that Excel stores dates and times as numbers. Dates as an integer (the number of dates since Jan 1st 1900?), and times as a fraction (8AM = .3333...) Simply subtract the start time from the end time. Also subtract any time off for lunch and breaks that should not be counted. The result might look funny as Excel might format the resulting cell as a date. Format THAT cell as a number and you'll have your elapsed time. > Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling > short of a sandwich when it comes to excel, so please keep that in mind if [quoted text clipped - 13 lines] > > Thanks Flick. I just didn't get it though. I did say I was a filling short of a sandwich though. :) SignatureI don't mind being wrong, I'd just like you to prove it! :) > Instead of the way you are doing it, remember that Excel stores dates and > times as numbers. Dates as an integer (the number of dates since Jan 1st [quoted text clipped - 23 lines] > > > > Actually, do this extra step - multiply by 24 (hours per day). The resulting formula should look like =(E14-D14)*24 where E14 is the end time, D14 is the start time > Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling > short of a sandwich when it comes to excel, so please keep that in mind if [quoted text clipped - 13 lines] > > If you really need to use that structure for recording time... try this: I4: =INT((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24) J4: =MOD((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24,1)*60 copy those formulas down as far as you need. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a > filling [quoted text clipped - 13 lines] > 5 08 | 30 12 | 30 13 | 00 17 | 30 > 6 09 | 00 12 | 00 12 | 40 17 | 00 Thanks Ron, The minutes are calculating a treat (J4) :) The hours though (I4) is returning all zero's. But thanks for what I got so far. SignatureI don't mind being wrong, I'd just like you to prove it! :) > If you really need to use that structure for recording time... > try this: [quoted text clipped - 30 lines] > > 5 08 | 30 12 | 30 13 | 00 17 | 30 > > 6 09 | 00 12 | 00 12 | 40 17 | 00 Hi again Ron, ignore my last message; I just changed the cell format and it's perfect. Thank you very much for your help. You guys are great. Doug SignatureI don't mind being wrong, I'd just like you to prove it! :) > If you really need to use that structure for recording time... > try this: [quoted text clipped - 30 lines] > > 5 08 | 30 12 | 30 13 | 00 17 | 30 > > 6 09 | 00 12 | 00 12 | 40 17 | 00 Thanks for the update, Doug...I'm glad we could help. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Hi again Ron, ignore my last message; I just changed the cell format and > it's [quoted text clipped - 42 lines] >> > 5 08 | 30 12 | 30 13 | 00 17 | 30 >> > 6 09 | 00 12 | 00 12 | 40 17 | 00 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.