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
--
Brevity is the soul of wit.


"Duncs" wrote:

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

Hi Duncs,

Look he

http://www.dicks-blog.com/archives/2...-addin-part-2/

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Duncs" wrote in message ups.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
| 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
|

try this where a1 is start and b1 is end

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



--
Don Guillett
SalesAid Software

"Duncs" wrote in message
ups.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
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

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

"Duncs" wrote in message
ups.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
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

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

Biff wrote:
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

"Duncs" wrote in message
ups.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
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

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

Don Guillett wrote:
try this where a1 is start and b1 is end

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



--
Don Guillett
SalesAid Software

"Duncs" wrote in message
ups.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
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

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

Dave F wrote:
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
--
Brevity is the soul of wit.


"Duncs" wrote:

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

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 he

http://www.dicks-blog.com/archives/2...-addin-part-2/

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Duncs" wrote in message ups.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
| 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
|

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

Biff wrote:
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

"Duncs" wrote in message
ups.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
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

ToolsOptionsCalculation tab, check Automatic

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Duncs" wrote in message ups.com...
| 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 he
|
| http://www.dicks-blog.com/archives/2...-addin-part-2/
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Duncs" wrote in message ups.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
| | 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
| |
|

Niek,

Works a treat.

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

Duncs

Niek Otten wrote:
ToolsOptionsCalculation tab, check Automatic

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Duncs" wrote in message ups.com...
| 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 he
|
| http://www.dicks-blog.com/archives/2...-addin-part-2/
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Duncs" wrote in message ups.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
| | 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
| |
|

Get them fired!

--
Don Guillett
SalesAid Software

"Duncs" wrote in message
oups.com...
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

Dave F wrote:
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
--
Brevity is the soul of wit.


"Duncs" wrote:

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