Solved

Calculate number of weeks in a month in MS Excel 2013

Posted on 2016-09-11
12
72 Views
Last Modified: 2016-09-12
How to calculate number of weeks in a month in Excel 2013? Below is the Excel table that I am using.
Table
0
Comment
Question by:cbinayak
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41793364
Assuming the 1st of the month is entered in A1 as a date then

=DAY(EOMONTH(A1,0))/7
0
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41793366
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
 

Author Comment

by:cbinayak
ID: 41793372
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41793375
This works for me
WEEKS-IN-MONTH.xlsx
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41793378
01-10-206 isn't a date ...
1
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41793380
Well spotted. I didn't use the OP's table.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:cbinayak
ID: 41793410
Thanks. It's working now.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41793730
Pleased to help
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41794172
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41794197
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41794243
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41794278
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now