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.
Table
cbinayakAsked:
Who is Participating?
 
Roy CoxConnect With a Mentor Group Finance ManagerCommented:
This will give you exact weeks

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

This will give weeks & days

=INT(DAY(EOMONTH(A1,0))/7)&" weeks and "&DAY(EOMONTH(A1,0))-INT(DAY(EOMONTH(A1,0))/7)*7 &" days"
0
 
Roy CoxGroup Finance ManagerCommented:
Assuming the 1st of the month is entered in A1 as a date then

=DAY(EOMONTH(A1,0))/7
0
 
cbinayakAuthor Commented:
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.
Excel Formula
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Roy CoxGroup Finance ManagerCommented:
This works for me
WEEKS-IN-MONTH.xlsx
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
01-10-206 isn't a date ...
1
 
Roy CoxGroup Finance ManagerCommented:
Well spotted. I didn't use the OP's table.
0
 
cbinayakAuthor Commented:
Thanks. It's working now.
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Dr Christophe TrefoisResearcher / IT SpecialistCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.