number of weeks in a month

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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
 
Upvote 0
try this
Excel Workbook
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
Sheet1
Excel 2003
Cell Formulas
RangeFormula
A1=EOMONTH(TODAY(),-1)+1
A2=EOMONTH(TODAY(),ROW()-2)+1
A3=EOMONTH(TODAY(),ROW()-2)+1
A4=EOMONTH(TODAY(),ROW()-2)+1
A5=EOMONTH(TODAY(),ROW()-2)+1
A6=EOMONTH(TODAY(),ROW()-2)+1
A7=EOMONTH(TODAY(),ROW()-2)+1
A8=EOMONTH(TODAY(),ROW()-2)+1
A9=EOMONTH(TODAY(),ROW()-2)+1
A10=EOMONTH(TODAY(),ROW()-2)+1
A11=EOMONTH(TODAY(),ROW()-2)+1
A12=EOMONTH(TODAY(),ROW()-2)+1
B1=IF(MONTH(A1)<>12,WEEKNUM(A2)-WEEKNUM(A1),52-WEEKNUM(A1)+1)
B2=IF(MONTH(A2)<>12,WEEKNUM(A3)-WEEKNUM(A2),52-WEEKNUM(A2)+1)
B3=IF(MONTH(A3)<>12,WEEKNUM(A4)-WEEKNUM(A3),52-WEEKNUM(A3)+1)
B4=IF(MONTH(A4)<>12,WEEKNUM(A5)-WEEKNUM(A4),52-WEEKNUM(A4)+1)
B5=IF(MONTH(A5)<>12,WEEKNUM(A6)-WEEKNUM(A5),52-WEEKNUM(A5)+1)
B6=IF(MONTH(A6)<>12,WEEKNUM(A7)-WEEKNUM(A6),52-WEEKNUM(A6)+1)
B7=IF(MONTH(A7)<>12,WEEKNUM(A8)-WEEKNUM(A7),52-WEEKNUM(A7)+1)
B8=IF(MONTH(A8)<>12,WEEKNUM(A9)-WEEKNUM(A8),52-WEEKNUM(A8)+1)
B9=IF(MONTH(A9)<>12,WEEKNUM(A10)-WEEKNUM(A9),52-WEEKNUM(A9)+1)
B10=IF(MONTH(A10)<>12,WEEKNUM(A11)-WEEKNUM(A10),52-WEEKNUM(A10)+1)
B11=IF(MONTH(A11)<>12,WEEKNUM(A12)-WEEKNUM(A11),52-WEEKNUM(A11)+1)
B12=IF(MONTH(A12)<>12,WEEKNUM(A13)-WEEKNUM(A12),52-WEEKNUM(A12)+1)
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top