• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate the number of weeks in a month?

I

Iamvwxyz

New Member
Hi,


I am having a row filled with the data like:

7/1/2013 7/8/2013 7/15/2013 7/22/2013 7/29/2013 8/5/2013 8/12/2013 8/19/2013 8/26/2013 9/2/2013 9/9/2013 9/16/2013 9/23/2013 9/30/2013


As you can see, in July'13 there are 5 weeks & in Aug'13 there are only 4 weeks.

How do I write an excel formula or a macro to calculate the number of weeks in a particular month? please advice.
 
Hi URvwxyz!


Welcome to the forum.. :)

Week is dependent on SunDay / Monday.. but Month is dependent on 1 / 2.. so Its not possible.. to say that.. every 1st date of month is 1st Week.. for that month.. :)


But check the below post.. and let us know.. if you are facing any problem to adapting it.. :)


http://chandoo.org/forums/topic/i-want-only-4-5-weeknum-in-a-month


Regards,

Deb
 
Looking at your dates, you are counting how many Monday's there are in a month rather than how many full weeks. In which case the below formula will work where A1 contains the first date of the month in question (i.e. 07/01/2013 or 08/01/2013 etc).


=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))=2))


it will require you having the analysis toolpak installed to use EOMONTH (Tools - Add-Ins - Analysis Toolpak)
 
Actually - a more elegant way is posted here: http://www.ozgrid.com/forum/showthread.php?t=21396 where Dave Hawley has created a nice function in VBA.
 
You must log in or register to reply here.
Back
Top