Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Number of week in month (Excel xp)

    I have a user that will enter the date, ie 9/14/03. From that i need to figure out what number week this is. I need to return a 3 in this case for the 3rd week. Is there an excel function to do this? thank you for the help.

    ps: i tried the weeknum function which gives me a 37. I need the number of the week in that month, 1 to 5 being possible.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Number of week in month (Excel xp)

    Try:
    <pre>=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1))+1</pre>


    Weeknum is from beg of year, so subtract from 1st of month.

    Steve

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Number of week in month (Excel xp)

    Hi jha,

    If you need to know what week of the month the date is, with dates on the 1st-7th being in the first week regardless of which week of the year they're in, and your date is in A1, this should work:

    =INT((day((A1))-1)/7+1)

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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