Getting "Week of Month"

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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
 
Upvote 0
That's excellent, thanks all for the help, just need the AddIn.
Rgds Ted
 
Upvote 0
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)
 
Upvote 0
Re: Getting "Week of Month"

Hi All.. Guess Im posting too late,

I'm having a problem with the month of January :confused:

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
 
Upvote 0
Re: Getting "Week of Month"

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?
 
Upvote 0
Re: Getting "Week of Month"

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.
 
Upvote 0
Re: Getting "Week of Month"

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?
 
Last edited:
Upvote 0
Re: Getting "Week of Month"

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

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top