 |
 | 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 2007Hours Between Two Times
Is there a way to caluculate the hours between two dates and times while excluding weekends and holidys? Example: Start - 8/24/2007 12:00pm Stop - 8/27/2007 12:00pm Result = 24 hrs Thanks RN Hi RN, Assuming that your start date/time is in cell A2 and Stop date/time is in cell A3. I'll give you 2 nested formulas. One if you have the cell formatted for hrs and mins and one if you want the cell formatted as a standard number. Example 1:- Custom format the cell where you want the formula to [h] if you only want to see hours or [h]:mm if you want to see hours and minutes (The square brackets around the h force it to display times greater than 24 hours correctly). Then copy the following formula to the cell:- =(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)) Example 2:- Format the cell where you want the formula to a number (with or without decimal places). Then copy the following formula to the cell:- =((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24 Hope it works as you want and if it does then if you posted via the MS Communities website then don't forget to answer 'yes' to the question was this helpful. Regards, OssieMac > Is there a way to caluculate the hours between two dates and times while > excluding weekends and holidys? [quoted text clipped - 9 lines] > > RN One other thing. If you want to exclude holidays then you will need to look up the NETWORKDAYS function in help and create the list of holidays and add the holidays option into the formula. Regards, OssieMac Hi OssieMac, I pasted your formula where start date/time was in F5 and stop date/time was in G5 and received an VALUE error message. Can you help me understand what happened? Exact formula: =(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5)) thanks JS > Hi RN, > [quoted text clipped - 38 lines] > > > > RN Are you sure that F5 and G5 contain real dates, and not just text values that look like dates? Try changing the format of both cells to general, and see if there is any change - a real date/time will look something like 39412.97708 (today's date and time). Hope this helps. Pete On Nov 26, 11:00 pm, JStafford <JStaff...@discussions.microsoft.com> wrote: > Hi OssieMac, > [quoted text clipped - 51 lines] > > - Show quoted text - Pete - Thanks for the suggestion. I checked it and when I format to general I do get something like below (actually got 39395.70833), so that isn't the problem. thx Jon > Are you sure that F5 and G5 contain real dates, and not just text > values that look like dates? Try changing the format of both cells to [quoted text clipped - 62 lines] > > > > - Show quoted text - Also check that G5 is finish date and is greater than F5 which is start date. That is the finish date/time must be greater than the start date/time because you cannot have negative date/time. I copied your formula to a worksheet and inserted a finish date in G5 and start date in F5 and it works perfectly.  SignatureRegards, OssieMac > Are you sure that F5 and G5 contain real dates, and not just text > values that look like dates? Try changing the format of both cells to [quoted text clipped - 62 lines] > > > > - Show quoted text - Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your time, but could I have the G5 and F5 formatted incorrectly? I formatted them as: dddd m/d/yyyy h:mm AM/PM I'm just at a loss - this should be simple and I'm spending hours trying to figure it out! Thanks for the input. Jon > Also check that G5 is finish date and is greater than F5 which is start date. > That is the finish date/time must be greater than the start date/time because [quoted text clipped - 69 lines] > > > > > > - Show quoted text - Hi Jon, My test results:- Cell F5: Friday 11/9/2007 11:00 AM Cell G5 Tuesday 11/13/2007 1:15 PM Formula in cell H5 (can be in any cell):- =(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5)) Returned answer in cell formatted as [h]:mm :- 50:15 Answer looks correct to me. #VALUE error is more like the values are not valid dates. SignatureRegards, OssieMac > Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your > time, but could I have the G5 and F5 formatted incorrectly? I formatted them [quoted text clipped - 79 lines] > > > > > > > > - Show quoted text - Hi Jon, This could be your problem. You need the Analysis Tool Pak Addin for NETWORKDAYS function. Tools->Options->Add-ins and check the box. If not there, lookup in help about loading addins. SignatureRegards, OssieMac > Hi Jon, > [quoted text clipped - 96 lines] > > > > > > > > > > - Show quoted text - I would have thought that would produce the #NAME error, rather than #VALUE. Pete On Nov 27, 5:09 am, OssieMac <Ossie...@discussions.microsoft.com> wrote: > Hi Jon, > [quoted text clipped - 120 lines] > > - Show quoted text - Hi Pete, I unloaded the Analysis Toolpak addin and under test I get #VALUE. Selecting Show Calculation steps on the error box adjacent to the cell confirms it. SignatureRegards, OssieMac > I would have thought that would produce the #NAME error, rather than > #VALUE. [quoted text clipped - 127 lines] > > > > - Show quoted text - Ah! Right! Something to remember for the future. Thanks for feeding back. Pete On Nov 27, 8:55 pm, OssieMac <Ossie...@discussions.microsoft.com> wrote: > Hi Pete, > [quoted text clipped - 138 lines] > > - Show quoted text -
|
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.