Networkdays Stopped Working - Again

S

Steve

Hi there -

The networkdays function stopped working in all my workbooks today. I
**DO** have the Analysis Toolpak installed and I **DO** have the Automatic
Calcuation Option turned on. Any thoughts?

Thanks,
Steve
 
T

T. Valko

Networkdays Stopped Working - Again

Again meaning it's happened before?

How about a replacement formula that doesn't depend on the ATP?

A1 = start date
B1 = end date
C1:C5 = holiday list (if you're using the Holidays argument)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C5,0))))

If you're not using the Holidays argument:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))
 
S

Steve

Yes, networkdays has stopped before, I know that one time it was because I
had turned off the Automatic Calculation option.

Thanks for suggesting the formula. I'm sure that will work, I'm stumped as
to why it has stopped working.

Steve
 
T

T. Valko

Even though you say the ATP is loaded you can try reloading it just to make
double sure!
 
F

Fred Smith

What do you mean by stopped working? It doesn't calculate? It gives erroneous
results? Do other functions still work?

What formula are you using? What happens when you hit F9?
 
H

Harlan Grove

T. Valko said:
How about a replacement formula that doesn't depend on the ATP?

A1 = start date
B1 = end date
C1:C5 = holiday list (if you're using the Holidays argument)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),
--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C5,0))))

If you're not using the Holidays argument:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))
....

Yucky volatile functions!

Using placeholders,

=EndDate-StartDate
-COUNTIF(Holidays,">="&StartDate)+COUNTIF(Holidays,">"&EndDate)
-2*INT((EndDate-WEEKDAY(EndDate,2)-StartDate+WEEKDAY(StartDate,2))/7)
-SIGN(WEEKDAY(EndDate,2)-6)+(WEEKDAY(StartDate,2)=7)

which in prose is

period in days between the two dates
-holidays during the period
-full weekends during the period
-tricky correction term
 
D

David Biddulph

If youre getting a #NAME error and NETWORKDAYS is unavailable in the
function wizard, despite ATP supposedly being loaded, there is a known bug
where ATP sometimes gets lost.
Usually just shutting down Excel and restarting will solve the problem.
 
S

Steve

David -

Indeed, this was the "fix" - although I actually had to restart the
computer, not just Excel. (Fred - yes, I was getting the ?#NAME error.)

STeve
 
T

T. Valko

Harlan Grove said:
Using placeholders,

=EndDate-StartDate
-COUNTIF(Holidays,">="&StartDate)+COUNTIF(Holidays,">"&EndDate)
-2*INT((EndDate-WEEKDAY(EndDate,2)-StartDate+WEEKDAY(StartDate,2))/7)
-SIGN(WEEKDAY(EndDate,2)-6)+(WEEKDAY(StartDate,2)=7)

which in prose is

period in days between the two dates
-holidays during the period
-full weekends during the period
-tricky correction term

This works as long as the holiday dates don't fall on weekends. Does that
need to be taken into account? I guess it depends on where you work or the
application. Not all workplaces will adjust the holiday observance to the
preceding Friday or the following Monday. I used to work at a place that
wouldn't do that and I'm sure their reason was to avoid holiday pay.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top