 |
 | 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 / Worksheet Functions / March 2007Format Cells to calculate hours and minutes taken to complete task
Hi I have the following calculation in my column E: =((D3-C3)*24)-(NETWORKDAYS(C3,D3)*24)+24 Column D is Date/Time order completed in format: dd/mm/yyyy hh:mm:ss Column C is Date/Time order received in format: dd/mm/yyyy hh:mm:ss Currently my results in column E return 0.17 which equates to 10 minutes - I really want it to show 10 minutes and not 0.17 Can anyone help? Well, you could modify your formula so that the final result is divided by 24 (ie putting it back into Excel time) and then format the formula cell as an Excel time format ie [h]:mm (if tasks may take over 24 hours) Alternatively, you could multiply by 60 in which case you will end up with an integer value representing the number of minutes to complete the task. Hope this helps! Richard Hi Richard Grateful if you can tell me where in my formula I can make the changes you suggest because if I put /24 at the end vice +24 and format cell to [h]:mm then it returns 4:00 Any help would be greatly appreciated. Thanks Andy > Well, you could modify your formula so that the final result is > divided by 24 (ie putting it back into Excel time) and then format the [quoted text clipped - 11 lines] > > Richard Microsoft times are a multiple of days. I would not multiply by 24 hours. =((D3-C3))-(NETWORKDAYS(C3,D3))+1 The above formula will give you an answer in days. Then format the cell using a time format which should give you an accurate hh:mm format. > Hi > [quoted text clipped - 8 lines] > > Can anyone help? I think the right answer is very simple =D3-C3 Then format using time format. > Microsoft times are a multiple of days. I would not multiply by 24 hours. > [quoted text clipped - 15 lines] > > > > Can anyone help? Joel Excellent - works perfectly.....many many thanks Regards Andy > Microsoft times are a multiple of days. I would not multiply by 24 hours. > [quoted text clipped - 15 lines] > > > > Can anyone help? |
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.