 |
 | 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 / November 2006Can you write your own NETWORKDAYS function?
For one reason or another, which no one can seem to answer, we cannot access the "Analysis Toolpak" addin. It isn't there to install, and our systems guys refuse to run the setup program to make it available due to, "a possibility of possible system instability", or some such garbage reason! I've argued until I'm blue in the face, but it makes no difference. They wont install it. So, my next mission is to see if I can write one, or if someone else has written one. I need it for a spreadsheet I'm creating, and can't think of a way round it. So, if anyone has any ideas / suggestions / hints / tips / pointers / code lying around that would help me, I'd be grateful. Many thanks Duncs One thought: Create a list of the days in the year. In the column to the right of the list code holidays and weekends "Invalid" or some other marker. Then use COUNTIF to count the number of VALID days between the two days. As for the Analysis Toolpak causing "system instability"--do your IT guys realize that it's MSFT-provided software? Dave  SignatureBrevity is the soul of wit. > For one reason or another, which no one can seem to answer, we cannot > access the "Analysis Toolpak" addin. It isn't there to install, and [quoted text clipped - 13 lines] > > Duncs Dave, I did think on that, but it would mean updating it every year to reflect the new holiday dates, changes in local holidays and of course, the yearly changes to Easter. As regards the "system instability" issues, I've pleaded and begged them to install it, but they are not for shifting. I've explained that it's suppl;ied by MS and that it will cause no instability problmes, but they're not for budging. Duncs > One thought: Create a list of the days in the year. In the column to the > right of the list code holidays and weekends "Invalid" or some other marker. [quoted text clipped - 24 lines] > > > > Duncs Get them fired! SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Dave, > [quoted text clipped - 38 lines] >> > >> > Duncs Hi Duncs, Look here: http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-add in-part-2/ SignatureKind regards, Niek Otten Microsoft MVP - Excel | For one reason or another, which no one can seem to answer, we cannot | access the "Analysis Toolpak" addin. It isn't there to install, and [quoted text clipped - 13 lines] | | Duncs Niek, I've looked at the site and entered the formula. I've changed the cell values and waited. When I enter the formula in the first row, it gives me the correct value. When I copy it into the next row, it displays the same value that it had in the first row. When I copy it into the third row, it again displays the same value as rows one & two. However, if I press F9 to refresh, the cells display the correct values. Is this an issue with the absence of the Analysis Toolpak, in that you get no automatic data refresh? Rgds Duncs > Hi Duncs, > [quoted text clipped - 25 lines] > | > | Duncs Tools>Options>Calculation tab, check Automatic SignatureKind regards, Niek Otten Microsoft MVP - Excel | Niek, | [quoted text clipped - 43 lines] | > | | > | Duncs Niek, Works a treat. Many thansk for your, and everyone elses help / suggestions. Duncs > Tools>Options>Calculation tab, check Automatic > [quoted text clipped - 51 lines] > | > | > | > | Duncs try this where a1 is start and b1 is end =SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7)) SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > For one reason or another, which no one can seem to answer, we cannot > access the "Analysis Toolpak" addin. It isn't there to install, and [quoted text clipped - 13 lines] > > Duncs Don, Your formula works, the only problem being, my spreadsheet does not automatically update. Is this a problem due to the lack of the analysis toolpak? Duncs > try this where a1 is start and b1 is end > [quoted text clipped - 21 lines] > > > > Duncs Start date in A1 End date in B1 To exclude holidays: J1:J10 = list of holiday dates to be excluded from the calculation =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),J1:J10,0)))) Biff > For one reason or another, which no one can seem to answer, we cannot > access the "Analysis Toolpak" addin. It isn't there to install, and [quoted text clipped - 13 lines] > > Duncs Biff, I've tried your formula, and in my current spreadsheet I get a value of 2 for the number of elapsed days, regardless of the dates entered. When i try it in a brand new spreadsheet, using just the cells referenced by your formula, I get a value of 0 for the elapsed days, again regardless of the dates entered. Any ideas? Duncs > Start date in A1 > End date in B1 [quoted text clipped - 24 lines] > > > > Duncs Sorry Biff, I was a little quick off the mark with my comment there! The formula does work, it's just that the spreadsheet does not automatically update. Any ideas? Duncs > Start date in A1 > End date in B1 [quoted text clipped - 24 lines] > > > > Duncs |
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.