Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Getting "Week of Month"

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's excellent, thanks all for the help, just need the AddIn.
    Rgds Ted

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

    Default

    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)

  6. #6
    Board Regular swaroop's Avatar
    Join Date
    Nov 2009
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting "Week of Month"

    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
    Cheers,

    SS

  7. #7
    Board Regular
    Join Date
    Aug 2009
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  8. #8
    Board Regular swaroop's Avatar
    Join Date
    Nov 2009
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    SS

  9. #9
    Board Regular
    Join Date
    Aug 2009
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by bRIAN tHOMAS; Jan 4th, 2010 at 01:01 AM.

  10. #10
    Board Regular swaroop's Avatar
    Join Date
    Nov 2009
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting "Week of Month"

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

    SS

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
  •