Networkdays Function

[Ron Bridgeman]

When I enter "= NETWORKDAYS(DATE(2008,3,1),NOW())" into a cell in a
worksheet, it calculates perfectly. However, the second time that I close and
reopen the workbook, I receive a "#N/A" error. (I can close and reopen the
workbook once without an error.) The Analysis ToolPak is installed.

Can anyone explain why this is happening? Thanks

 

[Ron Bridgeman]

Workbook in question is password protected. When I unprotect it, the problem
goes away. Don't know why password protection would cause a problem, but is
there any way to deal with this and retain password protection?

 

[Ross OZ]

Ron
Try using "TODAY" instead of "NOW"
I have had problems in other areas and the "TODAY" as fixed it.
"NOW" is to the second and "TODAY" is to the day only.
Hope it works for you

 

[Ron Bridgeman]

Thanks. I tried it, but it didn't help. I appreciate your response.
Ron

 

[JP]

Try un-installing and re-installing the ATP, that usually works for
me.


HTH,
JP

 

[joeu2004]

Off-topic....

Does it bother anyone else that NETWORKDAYS has nothing to do with the
Internet (aka "networking")?

And where is the function that computes "gross" work days?

8-] 8-]

 

[JP]

Or "networking" as in making business contacts...?

--JP

 

[Gord Dibben]

joeu2004

"NET" is a valid term as in "net profits" so why should it bother you when Excel
uses it?

"gross workdays" function is NETWORKDAYS function with no designated holidays
range.

Or it could be just a count of cells with days worked entered.


Gord Dibben MS Excel MVP

Or "networking" as in making business contacts...?

--JP

Off-topic....

Does it bother anyone else that NETWORKDAYS has nothing to do with the
Internet (aka "networking")?

And where is the function that computes "gross" work days?

8-] 8-]

 

[joeu2004]

"NET" is a valid term as in "net profits" so why should it bother you
when Excel uses it?

"gross workdays" function is NETWORKDAYS function with no
designated holidays range.

My point exactly: I don't believe there is a single business person
who thinks of, much less refers to, "net" work days, meaning all
("gross") work days less holidays.

Moreover, regarding your analogy, I would never create a function
called NETPROFIT() that actually returns gross profit if arguments
change. It violates the Principle of Least Surprise.

Arguably, simply WORKDAYS is a more resonable name -- kinda like
DAYS360. I suspect that was avoided because of its similarity to
WORKDAY. (Refer to "Psychology of Computer Programming".)

But we are taking my comments too seriously. I was just pointing out
that I have to take a double-take every time someone asks about
NETWORKDAYS in these forums because my brain is wired (no pun
intended) to see that as "network days", not "net work days".
(Ignoring the nonsensical notion of "net" work days, in the first
place.)

If your brain is wired differently, more power to you.

 

[Ron Bridgeman]

Thanks. I uninstalled and reinstalled a couple of times, but that didn't
help. There seems to be a problem with the timing of the Networkdays
function execution, i.e., "Networkdays" trying to execute before all the
information it needs is available. The nested functions in the expression
execute as they should. I have tested them separately. It might be helpful if
I understood what happens when Excel starts up, but I don't.

Thanks again.

 

[George Nicholson]

My brain is wireless, but I have trouble picking up a signal sometimes.

--
HTH,
George

"NET" is a valid term as in "net profits" so why should it bother you
when Excel uses it?

"gross workdays" function is NETWORKDAYS function with no
designated holidays range.

My point exactly: I don't believe there is a single business person
who thinks of, much less refers to, "net" work days, meaning all
("gross") work days less holidays.

Moreover, regarding your analogy, I would never create a function
called NETPROFIT() that actually returns gross profit if arguments
change. It violates the Principle of Least Surprise.

Arguably, simply WORKDAYS is a more resonable name -- kinda like
DAYS360. I suspect that was avoided because of its similarity to
WORKDAY. (Refer to "Psychology of Computer Programming".)

But we are taking my comments too seriously. I was just pointing out
that I have to take a double-take every time someone asks about
NETWORKDAYS in these forums because my brain is wired (no pun
intended) to see that as "network days", not "net work days".
(Ignoring the nonsensical notion of "net" work days, in the first
place.)

If your brain is wired differently, more power to you.

 

[Ron Bridgeman]

I finally found a workaround to replace the NETWORKDAYS function at the
website
http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/.
NETWORKDAYS EQUIVALENT
=SUMPRODUCT(-(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)))<>1),(-(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)))<>7)),-(COUNTIF(holidays,ROW(INDIRECT(start_date&":"&end_date)))=0))*(1-2*(start_date>end_date)).
This seems to work. Thanks for all comments.