 |
 | 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 / February 2008Time Calc > 24 hrs
I am trying to figure out how I can subtract 2 times: day 1=7:20 am and day 2 = 14:45 answer should be 31:25. I need to use the result when averaging the column. Formula used is : =IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be correct if within 24 hrs. TIA >I am trying to figure out how I can subtract 2 times: day 1=7:20 am and >day 2 = 14:45 answer should be 31:25. I need to use the result when >averaging the column. Formula used is : >=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be >correct if within 24 hrs. TIA oops typo should be 7:25 If you have the date and time information together in the cells (such as 04/02/2008 07:20 and 05/02/2008 14:45), then instead of MOD(S50-N50,1) you just want S50-N50. If you don't have the date with the time, you just need to add 1 to the result if you've got an extra day. In either case, format the result as [h]:mm, not as h:mm  SignatureDavid Biddulph >>I am trying to figure out how I can subtract 2 times: day 1=7:20 am and >>day 2 = 14:45 answer should be 31:25. I need to use the result when >>averaging the column. Formula used is : >>=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be >>correct if within 24 hrs. TIA oops typo should be 7:25 Thanks David....already tried as you suggested, however forgot about the "[h]" part. Worked perfect. Thanks again. > If you have the date and time information together in the cells (such as > 04/02/2008 07:20 and 05/02/2008 14:45), then instead of MOD(S50-N50,1) [quoted text clipped - 9 lines] >>>=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be >>>correct if within 24 hrs. TIA oops typo should be 7:25 Add the date in as well like: Day 1 3/2/2008 7:20 Day 2 4/2/2008 14:45 (British style dates) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I am trying to figure out how I can subtract 2 times: day 1=7:20 am and >day 2 = 14:45 answer should be 31:25. I need to use the result when >averaging the column. Formula used is : >=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be >correct if within 24 hrs. TIA Too quick on the trigger. Then custom format the cell as [h]:mm SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Add the date in as well like: > [quoted text clipped - 11 lines] >>=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be >>correct if within 24 hrs. TIA Tx Sandy for the input....Tried the format as [hh]:mm as well since my eye likes things "even" See no problems so far. I am using 07 and saving as 97-2003 since the folks at work have older versions. 07 complaining about loss of formatting when saving to older version...will have to wait until the morning and correct if necessary. > Too quick on the trigger. > [quoted text clipped - 15 lines] >>>=IF(COUNT(N50,S50)=2,MOD(S50-N50,1),"") giving me 7:35 which would be >>>correct if within 24 hrs. TIA |
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.