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

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


--
Biff
Microsoft Excel MVP


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

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

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


--
Biff
Microsoft Excel MVP


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

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

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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" wrote:

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


--
Biff
Microsoft Excel MVP


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

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?

--
Regards,
Fred


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

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.
--
David Biddulph

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

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

"David Biddulph" wrote:

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.
--
David Biddulph

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

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
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!

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

--
Biff
Microsoft Excel MVP

"Harlan Grove" wrote in message
...
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.

--
Biff
Microsoft Excel MVP