number of weeks in a month
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: number of weeks in a month

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Location
    USA
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default number of weeks in a month

     
    How do you calculate the number of weeks in a month in excel. I have a budge with the start date of each month in a cell and I want to compute the number of weeks for that start date's month. How do you do this?

    Thanks!

  2. #2
    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: number of weeks in a month

    That probably depends how you define it. If a month has 31 days how many weeks does it have? Is it just 31/7 = 4.43 or do you want an integer based on how many Sundays or Mondays there are in the month (for example)

    For the first one if you have the 1st of the month in A1 use this formula in B1

    =(32-DAY(A1+31))/7

    format B1 as number

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Location
    Boston, MA
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: number of weeks in a month

    You could get the number of days in that month then divide by 7. If the date is in cell A1, then use:

    Code:
    =DAY(DATE(YEAR(A1),MONTH(A1)+1,))/7

  4. #4
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,457
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: number of weeks in a month

    try this

    Sheet1
    AB
    11/1/20115
    22/1/20114
    33/1/20114
    44/1/20115
    55/1/20114
    66/1/20114
    77/1/20115
    88/1/20114
    99/1/20114
    1010/1/20115
    1111/1/20114
    1212/1/20114
    Excel 2003

    Worksheet Formulas
    CellFormula
    A1=EOMONTH(TODAY(),-1)+1
    B1=IF(MONTH(A1)<>12,WEEKNUM(A2)-WEEKNUM(A1),52-WEEKNUM(A1)+1)
    A2=EOMONTH(TODAY(),ROW()-2)+1
    B2=IF(MONTH(A2)<>12,WEEKNUM(A3)-WEEKNUM(A2),52-WEEKNUM(A2)+1)
    A3=EOMONTH(TODAY(),ROW()-2)+1
    B3=IF(MONTH(A3)<>12,WEEKNUM(A4)-WEEKNUM(A3),52-WEEKNUM(A3)+1)
    A4=EOMONTH(TODAY(),ROW()-2)+1
    B4=IF(MONTH(A4)<>12,WEEKNUM(A5)-WEEKNUM(A4),52-WEEKNUM(A4)+1)
    A5=EOMONTH(TODAY(),ROW()-2)+1
    B5=IF(MONTH(A5)<>12,WEEKNUM(A6)-WEEKNUM(A5),52-WEEKNUM(A5)+1)
    A6=EOMONTH(TODAY(),ROW()-2)+1
    B6=IF(MONTH(A6)<>12,WEEKNUM(A7)-WEEKNUM(A6),52-WEEKNUM(A6)+1)
    A7=EOMONTH(TODAY(),ROW()-2)+1
    B7=IF(MONTH(A7)<>12,WEEKNUM(A8)-WEEKNUM(A7),52-WEEKNUM(A7)+1)
    A8=EOMONTH(TODAY(),ROW()-2)+1
    B8=IF(MONTH(A8)<>12,WEEKNUM(A9)-WEEKNUM(A8),52-WEEKNUM(A8)+1)
    A9=EOMONTH(TODAY(),ROW()-2)+1
    B9=IF(MONTH(A9)<>12,WEEKNUM(A10)-WEEKNUM(A9),52-WEEKNUM(A9)+1)
    A10=EOMONTH(TODAY(),ROW()-2)+1
    B10=IF(MONTH(A10)<>12,WEEKNUM(A11)-WEEKNUM(A10),52-WEEKNUM(A10)+1)
    A11=EOMONTH(TODAY(),ROW()-2)+1
    B11=IF(MONTH(A11)<>12,WEEKNUM(A12)-WEEKNUM(A11),52-WEEKNUM(A11)+1)
    A12=EOMONTH(TODAY(),ROW()-2)+1
    B12=IF(MONTH(A12)<>12,WEEKNUM(A13)-WEEKNUM(A12),52-WEEKNUM(A12)+1)


  5. #5
    New Member
    Join Date
    Oct 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: number of weeks in a month

    =INT(DAY(EOMONTH(A1,0))/7)&" Weeks & "&MOD(DAY(EOMONTH(A1,0)),7)&" Days"

  6. #6
    New Member
    Join Date
    Nov 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: number of weeks in a month

      
    =INT(DAY(EOMONTH(A1,0))/7)+(WEEKDAY(A1,3)>4)
    =INT(DAY(EOMONTH(RC1,0))/7)+(WEEKDAY(RC1,3)>4)

    +(WEEKDAY(A1,3)>4) intended to be correction that accommodates the 'extra' week - this is not quite right but shows the principle - I am sure some playing will resolve satisfactorily

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
  •  

 

 
DMCA.com