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

 

[jetboy2k]

Re: Getting "Week of Month"

But, much more importantly, it turns out that Sankar's solution at the very top of page 2 does what I needed MUCH more simply, and without any inconsistency whatsoever. Not sure how I missed it earlier, but it is a truly clever and elegant solution.

 

[barry houdini]

Re: Getting "Week of Month"

The difference with that version is that it takes no account of the day of week, you simply get week 1 for the first 7 days of the month, Week 2 for the next 7 days etc.

 

[jetboy2k]

Re: Getting "Week of Month"

The difference with that version is that it takes no account of the day of week, you simply get week 1 for the first 7 days of the month, Week 2 for the next 7 days etc.

Correct. Buried deep in my original post is a reference to actually needing that, but given the <sarcsasm><sarcasm>brevity</sarcasm></sarcasm> of that post, I can see that it would be easily missed. And, for many folks that need a running count, this particular result may well not meet their requirements. Having played around with the initial formula, and your correction, I'm coming to the realization that, realistically, I think it would be necessary to write a VBA snippet, or a plug-in, to really deliver the functionality in a way that could meet the multiple potential requirements various users would need.

It seems so simple until you actually need to do it yourself.