Weeknum Goes Missing

[Peter W]

Hi
I am using WEEKNUM function in a series of MACRO routines. Problem is they
work really well than sometimes for no apparent reason I get the #NAME error
command. I go back into the Add-Ins Menu, remove the selection on Analysis
Tool Pak, go out. Then go back in and re-select then Analysis Tool Pack - it
then works again. It took me a little while to work through this fix - and
lot of hair pulling!!

Does anyone have any suggestions as to what may be causing it?

Regards

 

[nbrcrunch]

Can't answer why the function is failing on you, but have you considered
writing your own weeknum() function. It can't be that hard. Here's a
simple formula to return the weeknum value:

=INT((TODAY()-DATE(YEAR(NOW()),1,1))/7)

The problem with the above is that it calculates the number of 7-day
weeks that have pasted, not the numeric position of the week we are
currently in, but my point is that a home-crafted formula might be a
solution.

(The above formula returns "6" used today, 2/16/2005 whereas weeknum()
returns "8".)

 

[Peter W]

Thanks for the suggestion - which I will use. But I would still like to know
why I have to trick Weeknum to work.

Regards

 

[nbrcrunch]

Update:

I recently found on Pearson's website both a formula and VBA code for
calculating week numbers without the need for the Analysis Toolkit
add-in.

I tested the formula against the weeknum() function for all dates in
this current (2005) calendar year and it returned the same value for
each date.

First, credit where credit is due:
http://www.cpearson.com/excel/DateTimeWS.htm
http://www.cpearson.com/excel/weeknum.htm

See the first link for the formula he recommends and the second link
for a VBA module that will do the same thing.