Convert NETWORKDAYS

[mutrus]

Hi all

Can anybody please convert this function so that users without the Analysis Pack installed can use it

NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,0),HOLIDAYS)

Thanks

 

[Aladin Akyurek]

On 2002-09-22 13:25, mutrus wrote:
Hi all

Can anybody please convert this function so that users without the Analysis Pack installed can use it

NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,0),HOLIDAYS)

Thanks

Due to George Simms:

=(INT(B1/7)-INT(A1/7))*5+MAX(0,MOD(B1,7)-1)-MAX(0,MOD(A1,7)-2)

Either replace B1 in the above formula with:

(DATE(YEAR(A1),MONTH(A1)+1,0))

or in B1 enter:

=DATE(YEAR(A1),MONTH(A1)+1,0)

The formula does not handle HOLIDAYS.


Maybe you should ask for code that activates the Analysis Toolpak add-in on user machines.

 

[PaddyD]

"The formula does not handle HOLIDAYS. "


which you could do by adding a

-counta(holidays)

to the formula...

Paddy

 

[Aladin Akyurek]

On 2002-09-22 13:53, PaddyD wrote:
"The formula does not handle HOLIDAYS. "


which you could do by adding a

-counta(holidays)

to the formula...

Paddy,

That's risky... Some holidays will not fall in the range of the dates of interest.

Aladin

 

[PaddyD]

True, true - must remember not to post before I've had at least 2 coffees!

Taking Aladin's recommendation to move DATE(YEAR(A1),MONTH(A1)+1,0) from within the formula to a cell, (eg B!),


-(countif(holidays,">="&A1)-countif(holidays,",="&B1))

Paddy

 

[Yogi Anand]

Hi Paddy:

Still one of the holidays could be on a weekend -- a non-workday -- could it not?

Regards!

Yogi

Edit: one could check which holidays fell on workdays before deducting!
This message was edited by Yogi Anand on 2002-09-22 14:32

 

[PaddyD]

Possible in principle, but I don't know of any national holiday that's not on a workday!

Paddy

 

[mutrus]

Hey thanks everybody.

It works better for me to have the holidays as a single value in another cell and then just subtract that off the formula. We have some instances where a non-working day may not be a public holiday at all (great life isn't it)

Reason for not having Analysis Toolpack on each client PC is that the spreadsheet could be used on a number of different networks and as users want to start using straight away this is easier than to wait until the LAN managers get around to upgrading each PC. I know this should be an easy task but how many times has productivity been lacking due to waiting for the IT guys to finish their coffee (no insults meant anybody). I'll upgrade to the NEWORKDAYS model once thats complete

Anyway thanks again