Networkdays Stopped Working - Again

[Guest]

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

 

[Guest]

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. Valko]

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

 

[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?

 

[Harlan Grove]

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

 

[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.

 

[Guest]

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. Valko]

...

Yucky volatile functions!

Yeah, but it's only a single formula. Of course, we don't know if they need
1000's of these.

 

[T. Valko]

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.