Getting "Week of Month"

[eddy]

Hi ..

I need to print the week of the month e.g. 1,2,3,4
I saw this code example on the board recently.
=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))
With =NOW() in A1 it dosn't seem to work (get #NAME error). Any suggestions?
Thanks Ted

[anno]

try =TODAY() in A1 instead

edit:
actually, it may be because you don't have the analysis toolpak installed - tools menu>add-ins

[ This Message was edited by: anno on 2002-05-09 01:40 ]

[Aladin Akyurek]

On 2002-05-09 01:13, eddy wrote:
Hi ..

I need to print the week of the month e.g. 1,2,3,4
I saw this code example on the board recently.
=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))
With =NOW() in A1 it dosn't seem to work (get #NAME error). Any suggestions?
Thanks Ted

WEEKNUM is only available by adding in the Analysis Toolpak thru Tools|Add-Ins.

Does this formula compute the week number the way you want?

Aladin

[eddy]

That's excellent, thanks all for the help, just need the AddIn.
Rgds Ted

[goggixja]

to consider partial first week of the month as week one; ie Thursday June 1, 2006 thru Saturday June 4, 2006, which will often create "5th" week of the month as seen on Caladars, use the equation below:

=WEEKNUM(A2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),0))+(WEEKNUM(A2)>2)

Note, insert ",2" in weeknum calculation to begin week on Monday:

=WEEKNUM(A2,2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),0),2)+(WEEKNUM(A2,2)>2)

[swaroop]

Hi All.. Guess Im posting too late,

I'm having a problem with the month of January

If the date in A1 is 1Jan2010 and by using "=WEEKNUM(G11)-WEEKNUM(DATE(YEAR(G11),MONTH(G11),0))+(WEEKNUM(G11)>2)" i get -52 as the week number

Please help

[bRIAN tHOMAS]

I don't know if this means anything but is it because Jan 1st was a friday, and so the tail end of the 52nd week of last year? Does it work fine if you use the 4th?

[swaroop]

Thanks for the reply.. Its something like this :

In Jan:
For the dates 1-3 I get -52
For 3-10 I get -51
For 11-18 I get -49
For 19- 24 I Get -48
So on...

From Feb Its perfect.

[bRIAN tHOMAS]

Well that does seem weird. I don't have this add in so I can't help you test or anything but seem to me if it works for everything but januarary its got to be an input problem and not a problem with the function itself.
Does it break exactly at end of January..so jan 31st is broken but feb 1st works?
And it may sound silly but you aren't typoing January or something like that are you?

Not much help granted but might help someone else later

Edit: Just noticed the formula refers to g11 but you say your date is in a1? Is that correct?

[swaroop]

My Format is 1Jan2010 (ddmmmyyyy), and yes.. It breaks at Jan31 and worksperfect from Feb