 |
 | 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 2006Hours and minutes in excel
Hello, I am trying to create a timesheet spreadsheet and have come upon a problem. I have four cells containing start time(D6), lunch out(D7), lunch in(D8) and finish time(D9). EG: 08:30 AM (start) 01:00 PM (lunch out) 02:00 PM (lunch in) 06:00 PM (finish) 08:30 (total) The total in D10 returns the total time as 08:30 using the formula =(D7<D6)+D7-D6+(D9<D8)+D9-D8 I need this return as 8.5 hours but have searched high and low and can find nothing to make it work!! Any help would be greatly appreciated. Cheers, Tone Hi Excel stores times as fractions of a day. Multiply your whole formula by 24 and format the cell as General and you will get 8.5 =((D7<D6)+D7-D6+(D9<D8)+D9-D8)*24  SignatureRegards Roger Govier > Hello, > [quoted text clipped - 23 lines] > > Tone That's brilliant! Many Thanks, Tone -- Ciao Thunder > Hi > [quoted text clipped - 34 lines] > > > > Tone Sure the solution of Roger is the best one. Mine is alone an alternative. Regards Thunder Oops! False shipment :-P Sure the solution of Roger is the best one. Mine is alone an alternative. Regards Thunder > Hi > [quoted text clipped - 34 lines] > > > > Tone > Hello, > [quoted text clipped - 23 lines] > > Tone In cell D11 you can insert: =(HOUR(D10)*60+MINUTE(D10))/60 Regards Thunder |
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
|
©2009 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.