# Calculate number of weeks in a month in MS Excel 2013

How to calculate number of weeks in a month in Excel 2013? Below is the Excel table that I am using.

Last Comment
Dr Christophe Trefois

8/22/2022 - Mon
Roy Cox

Assuming the 1st of the month is entered in A1 as a date then

=DAY(EOMONTH(A1,0))/7
Roy Cox

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
cbinayak

While using the below formula I am getting error.

=INT(DAY(EOMONTH(B2,0))/7) &" weeks"

However, =INT(DAY(EOMONTH(B2,0))/7) is working fine but not getting any result for other months.
Roy Cox

This works for me
WEEKS-IN-MONTH.xlsx
Qlemo

01-10-206 isn't a date ...
Roy Cox

Well spotted. I didn't use the OP's table.
cbinayak

Thanks. It's working now.
Roy Cox

Rob Henson

What was the point of the question?

All months have 4 weeks. Different months then have zero, two or three more days; 28 for Feb, 30 for April, June, September and November; all the rest have 31.

However, if you are counting only WHOLE weeks starting on a particular day, there could be differences.

For example, for the items listed in your table, assuming whole weeks starting on Sunday:

First Sunday in September was 4 September, last Saturday (to give whole weeks) in September is 24 September. 4 Sept to 24 Sept is 3 whole weeks.
October - First Sunday 02 Oct, Last Saturday 29 Oct, gives 4 weeks
November - First Sunday 06 Nov, Last Saturday 26 Nov, gives 3 weeks
December - First Sunday 04 Dec, Last Saturday 31 Dec, gives 4 weeks.

Thanks
Rob H
Rob Henson

This formula will give number of whole weeks:

=(IF(WEEKDAY(EOMONTH(N3,0))=7,EOMONTH(N3,0),FLOOR(EOMONTH(N3,0),7))-IF(WEEKDAY(N3,1)=1,N3,CEILING(N3,7)+1)+1)/7

Date in cell N3. The bold 7 assumes week ending Saturday, the bold 1 assumes week starting Sunday.
Rob Henson

Looks like you don't need the WEEKDAY check for the last day of the month:

=(FLOOR(EOMONTH(N3,0),7)-IF(WEEKDAY(N3,1)=1,N3,CEILING(N3,7)+1)+1)/7
Rob Henson

Prior formula assumes weeks beginning Sunday.

This assumes week beginning Monday, ie whole working weeks:

=(IF(WEEKDAY(EOMONTH(N3,0))=1,EOMONTH(N3,0),FLOOR(EOMONTH(N3,0),7)+1)-IF(WEEKDAY(N3,1)=2,N3,CEILING(N3,7)+2)+1)/7

Thanks
Rob H
Dr Christophe Trefois

Hi @Rob.

Your answer doesn't seem to work when the first of the month is a Sunday.

The corrected formula should be

=(IF(WEEKDAY(EOMONTH(N3,0))=1,EOMONTH(N3,0),FLOOR(EOMONTH(N3,0),7)+1)-IF(WEEKDAY(N3;1)=2;N3;N3+(7-WEEKDAY(N3;2)+1))+1)/7