number of weeks in a month

Thanks:  0
Likes:  0

# Thread: number of weeks in a month

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

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

6. ## 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

#### Posting Permissions

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