Thanks:  0
Likes:  0

# Thread: Getting "Week of Month"

1. ## Re: Getting "Week of Month"

see if this helps,

Sheet1
AB
115-Jan3
231-Jan5
301-Feb1
428-Feb4
Excel 2003

Worksheet Formulas
CellFormula
B1=ROUNDUP((A1-EOMONTH(A1,-1))/7,)

2. ## Re: Getting "Week of Month"

Originally Posted by 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

Yes I don't quite understand that formula (in the context of that thread anyway).

If you want the week number where a new week starts on a Sunday:

=WEEKNUM(G11)-WEEKNUMBER(DATE(YEAR(G11),MONTH(G11),1)+1

With this formula, you can actually have a week 6 in some months.

If you want the first 7 days of the month to be week 1, then next 7 week 2 etc:

=ROUNDUP(DAY(G11)/7,0)

-------
edit: beaten

3. ## Re: Getting "Week of Month"

Thanks Sankar and Adam, It worked!

4. ## Re: Getting "Week of Month"

=ROUNDUP(DAY(G11)/7,0)

-------
edit: beaten
I sholud tell that to you, missing the obvious

5. ## Re: Getting "Week of Month"

You need to do a MOD(xxx,52) to avoid having negative week numbers of the month.
"=MOD(WEEKNUM(G11)-WEEKNUM(DATE(YEAR(G11),MONTH(G11),0))+(WEEKNUM(G11)>2), 52)"

HTH

Hyperhelmi

6. ## Re: Getting "Week of Month"

Hi All! I have a question related to this thread. I would like to get the week of the month where week 1 starts only when the 1st of the month falls on a Thursday or before. Basically I am trying to analyze data following a policy meeting that occurs on the 1st Thursday of every month so I would like to analyze data from the following week (week 2). For eg. Feb 1st 2013 falls on a Friday so Week 1 would be Feb 4 - 10, Week 2 is Feb 11 - 17 etc.

7. ## Re: Getting "Week of Month"

No idea if anyone is still watching this thread, but I was having the same problem with the January dates. I started playing around with it and came up with this (somewhat inelegant, I admit, but it is working for me) solution. It uses four column, although it could probably be consolidated into fewer if desired:

1. Create the first column (I called it WeekendNumber)
2. Enter the following formula: =WEEKNUM(DATE(YEAR([@[Date Field]]),MONTH([@[Date Field]]),DAY([@[Date Field]])),2)
3. Create the second column (I called it LastWeekOfPrevMo)
4. Enter the following formula: =WEEKNUM(EOMONTH([@[Finish Date]],-1),2) (this gives you the week number of the last week of the previous month, relative to your [Date Field] value, for each row of the spreadsheet)
5. Create the third column (I called it RollingWkNum)
6. Enter the following formula: ==IF(MONTH([@[Date Field]])=1,[@WeekNumber],[@WeekNumber]-[@LastWkOfPrevMo]). I'll explain the IF condition for January below.
7. Create the fourth column (I called it AdjustedRollingWkNum)
8. Enter the following formula: =IF([@RollingWkNum]=0,1,[@RollingWkNum]) - (I'll explain why I needed this column below as well)

The result is that the week number in the fourth column "resets" and starts at 1 for each new month. The way this is designed, the first week of each month begins on the first day of the month, NOT on a Sunday, or a Monday, or some other day. This is actually what I needed, but if that's not what you need, it should be straightforward enough to modify the formulas to start each week on a selected day.

What this solution does is calculate the difference between a given week's number (i.e., it's weeknum() value, and the week number of the last week of date's previous month. So, for example, if you were looking at the third week in March, which had, say, a weeknum() value of 12, and the weeknum() value of the last week of February was 9, the result would be 3, which is the value I needed.

The reason that the formula in the third formula determines if the RollngWkNum valueis being calculated for January or not is because the month prior to January is December, for which the last week's weeknum() value is going to be 52 or 53. In this instance the difference between the weeknum() value of a week in January and the weeknum() value of the last week in December will not be the desired value, but since the weeknum() value for each week in January is the desired value anyway, for those dates you can just pull the weeknum() value directly.

The fourth column is needed because for every week that starts on a day other than Sunday (or Monday, or whichever day was selected as the first day of the week), the weeknum() value for the last week of the previous month and the first (partial) week of the current month will be the same, and the difference between the two will be 0. I didn’t want a “week 0” value, and since, as I mentioned, I actually wanted week 1 to start on the first day of the month, this column simply determines if this is the condition and if it is, sets the value to 1. If it’s not, it sets it to the value that was already calculated. The problem with this is that “week 1” of each month is actually the partial week and the full first week, but that was less of a problem for me than the week 0 value, so I was able to use this “fix.” If this had not been acceptable, I would have had to figure out a way to add the 1 to every RollingWkNum value, which, again, for me was not needed.

Hope this helps and, as I understand it, PowerPivot can actually map week numbers to each month, including the ability to restart the count with each new month, fairly simply, so this may be an outmoded solution anyway. But on the off chance that someone is still watching this thread, and still needs to do this, I figured I’d volunteer my solution.

Thanks.

8. ## Re: Getting "Week of Month"

Hello jetboy2k,

By doing it that way aren't you introducing an inconsistency between the numbering for January and the numbering for the rest of the year?

For example in 2014 January starts on Wednesday and has 31 days, October 2014 also starts on Wednesday and has 31 days but with that approach 20-26 January will be week 4 while 20-26 October is week 3

You could make January consistent with the other months by changing your column 3 formula to this

=IF(MONTH([@[Date Field]])=1,[@WeekNumber]-1,[@WeekNumber]-[@LastWkOfPrevMo])

The results you get with that change could also be obtained by a single formula to get the week number (in the month) direct from the date - with date in A2 you can use this:

=MAX(1,INT((DAY(A2)-WEEKDAY(A2-1))/7)+1)

9. ## Re: Getting "Week of Month"

Code:
`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?`
Yes it does.

In that case you could use ISO.WEEKNUMBER (ISO.WEEKNUMMER in my dutch version of excel 2013).

Here is a link to Wikipedia which explaination on this item.
(you could translate it in englisch)

http://nl.wikipedia.org/wiki/Weeknummer

10. ## Re: Getting "Week of Month"

Barry, good catch. It definitely does introduce the inconsistency you mention. Your fix still has a minor inconsistency in that "Week 1" of January actually includes all of the days of Week 0 and Week 1 for the year (the logic is too convoluted to type out but I'm guessing you can see why given that you already figured out the flaw in my logic), but that at least constrains the inconsistency to one week out of the year, instead of multiple weeks. And it would probably be fairly simple to fix it for January as a whole if needed.

Again, thanks for catching that, and for the fix.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•