Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Getting "Week of Month"

  1. #11
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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,)

    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  2. #12
    Board Regular
    Join Date
    Sep 2008
    Location
    Brisbane, Australia
    Posts
    767
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting "Week of Month"

    Quote Originally Posted by swaroop View Post
    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
    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. #13
    Board Regular swaroop's Avatar
    Join Date
    Nov 2009
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Getting "Week of Month"

    Thanks Sankar and Adam, It worked!
    Cheers,

    SS

  4. #14
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting "Week of Month"

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

    -------
    edit: beaten
    I sholud tell that to you, missing the obvious
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  5. #15
    New Member
    Join Date
    Feb 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #16
    New Member
    Join Date
    Oct 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Please help!! Thanks!

  7. #17
    New Member
    Join Date
    Oct 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #18
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)




    Last edited by barry houdini; Aug 29th, 2014 at 02:49 PM.

  9. #19
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Oeldere; Aug 29th, 2014 at 02:55 PM. Reason: link added

  10. #20
    New Member
    Join Date
    Oct 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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