Returning Week # per Month based on Date

Based on the Date/Month/Year, I want to return the week number the date is.


If cell a1 = 1/1/2012 then in b1 I want to return 1 (as it is in the 1st week of the month)
If cell a1 = 2/6/2012 then in b1 I want to return 2 (as it is in the 2nd week of the month)
If cell a1 = 1/31/2012 then in b1 I want to return 5 (as it is the 5th week of the month)
If cell a1 = 2/1/2012 then in b1 I want to return 1 (as it is in the 1st week of the month)

I have tried different versions of WeekNum formula but that isn't working, any ideas?

Any ideas?

00Able,

Give this a try:
=WEEKNUM(A2)-WEEKNUM(MONTH(A2)&"/1/"&YEAR(A2))+1

You can take some help from this thread - http://www.excelforum.com/excel-work...-in-month.html

The formula :=WEEKNUM(A2)-WEEKNUM(MONTH(A2)&"/1/"&YEAR(A2))+1

returns a 2 for the date 1/2/2012, which should be a one, then boosts every thing up a week, so 1/30 returns a 6? So it appears there is an issue with that formual, will continue to investigate...

00Able,

I am unable to duplicate the issues you described, see attached

Hello 00Able,

How are you defining the week numbers? Are the first 7 days of the month in week 1, the next 7 in week 2 etc.......or do you want each week to start on a specific day (like Monday or Sunday for example). If it's the latter then do you want some weeks that straddle months, so that week 5 could go into the next month or week 1 could start in the previous month.

If it's the first option (which fits all your examples) then try just

=INT((DAY(A1)+6)/7)

If that doesn't work for you then please explain the rules,

Alas I found my issue, I was using 2011 instead of 2012...=INT((13-WEEKDAY(A2)+DAY(A2))/7)
and
=WEEKNUM(A2)-WEEKNUM(MONTH(A2)&"/1/"&YEAR(A2))+1

both work great!!!
Thanks Guys!

I prefer the one without WEEKNUM but if you want to go with that version then Darkyam suggests a simplified non region-specific alternative (in the link posted by Arlette) to give the same result, i.e.

=WEEKNUM(A2)-WEEKNUM(A2-DAY(A2)+1)+1

Well I thought that was going to work but apparently it is not. It does work for what I originally stated, but since found out that the Week ending dates are always going to be Sunday.
So any day before and including the first Sunday would be in Week 1, anything before and including the 2nd Sunday would be in Week 2, etc...

So for:
1/8 would need to return 1
1/15 would need to return 2
1/22 would need to return 3
1/29 would need to return 4
1/31 would need to return 5
2/1 would need to return 1

Wish I woulda realized this in the first place...

Try this

=INT((13-WEEKDAY(A2,2)+DAY(A2))/7)

or this

=WEEKNUM(A2,2)-WEEKNUM(A2-DAY(A2)+1,2)+1

No luck both of those are returning 6 for 1/31 and 2 for 1/8

hello

maybe
=WEEKNUM(A2,21)

ooppps
2010 version.
ill try in 2003 ( sorry )

So, I looked at the equation again DaddyLongLegs and it appears it is doing exactly what it should...it appears because 1/1/2012 was a sunday, there is no week 1, as the first week entered was on 1/8, so the first week actually started on a non-work day...but it makes it look like we didn't work during week 1...hmmm not sure if there is a work around this exception, it only happens a few times a year...

@00Able from daddys formula.. I used 21...instead of 2 as stated in my previous post

=WEEKNUM(A1,21)-WEEKNUM(A1-DAY(A1)+1)+1

this is what i came ....

1/20/2012 3
1/31/2012 5
2/1/2012 1
1/15/2012 2
1/8/2012 1
1/22/2012 3
3/1/2012 1
5/1/2012 1
2/28/2012 5
5/1/2012 1
4/2/2012 1


when you used the 2 as return,, look at the date for 1/31/ 2012 ( 6 ) and 1/22/2010 ( 4 )

don't know if this is correct..and whats the difference between 21 and 2 as return add but they are both monday..Or i just have an error on my dates entry??????

My last suggestions were based on your previous stipulation

Originally Posted by 00Able

So any day before and including the first Sunday would be in Week 1, anything before and including the 2nd Sunday would be in Week 2, etc...

If 8th January is in week 1 then what week is 1st January in? If you can suggest a consistent rule then a formula can written to match it.

This is the only consistent thing I can think of...

=INT((13-WEEKDAY(A2,2)+DAY(A2))/7) this is correct unless A2 is both the 1st or 2nd day of the month and is either a Saturday or Sunday, then it needs to subtract 1

So because 1/1/2012 is a Sunday, technically we didnt have a week 1, because the work week runs Monday-Sunday and we rarely ever work Saturday or Sunday. So in that case I would need 1/8/2012 to return week 1, and Jan 1, 2012 would be week 0, as it wouldn't be necessary...seems complicated

OK try the same formula but with 11 in place of 13, i.e.

=INT((11-WEEKDAY(A2,2)+DAY(A2))/7)

I think that'll give you the results you want.....