Can you write your own NETWORKDAYS function?

D

Duncs

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
 
D

Dave F

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
 
N

Niek Otten

Hi Duncs,

Look here:

http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/

--
Kind 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
| 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
|
 
D

Don Guillett

try this where a1 is start and b1 is end

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))
 
B

Biff

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
 
D

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
 
D

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
 
D

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
 
D

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
 
D

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
 
N

Niek Otten

Tools>Options>Calculation tab, check Automatic

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
|
| Niek Otten wrote:
| > Hi Duncs,
| >
| > Look here:
| >
| > http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/
| >
| > --
| > Kind 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
| > | 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
| > |
|
 
D

Duncs

Niek,

Works a treat.

Many thansk for your, and everyone elses help / suggestions.

Duncs
 
Top