# Convert NETWORKDAYS

#### mutrus

##### Board Regular
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.

"The formula does not handle HOLIDAYS. "

which you could do by adding a

-counta(holidays)

to the formula...

"The formula does not handle HOLIDAYS. "

which you could do by adding a

-counta(holidays)

to the formula...

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

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))

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

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

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

Replies
7
Views
284
Replies
4
Views
348
Replies
4
Views
211
Replies
10
Views
291
Replies
2
Views
170

1,203,487
Messages
6,055,713
Members
444,810
Latest member
ExcelMuch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back