Find Number of Weeks in a Month

[Guest]

I have a macro that does various things to data in a worksheet. One of the
things I need to be able to do is for each month, find the number of weeks in
that month.

E.g. for April, how many weeks is there in that month - I only have the
month name to go by but I want it to check the number of weeks for April in
the current year.

Thanks

Sonya

 

[Don Guillett]

try where b2 contains april or apr

=(DATE(YEAR(NOW()),MONTH($B$2&"-0")+1,1)-DATE(YEAR(NOW()),MONTH($B$2&"-0"),1))/7

 

[Guest]

Thank You - just the ticket

try where b2 contains april or apr

=(DATE(YEAR(NOW()),MONTH($B$2&"-0")+1,1)-DATE(YEAR(NOW()),MONTH($B$2&"-0"),1))/7

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

When won't it be 4?

 

[Guest]

Actually after thinking about it - what I need is the number of Sundays in a
month, which is mostly 4 but occasionally 5.

 

[Guest]

Actually after thinking about it - what I need is the number of Sundays in a
month, which is mostly 4 but occasionally 5.

I found the answer in another thread

=4+(DAY(M2-DAY(M2)+1-WEEKDAY(M2-DAY(M2+7))+35)>7)

 

[Guest]

That formula won't give the number of Sundays in a month, assuming you have a
date in M2, any date in the month in question, then this formula will give
you a count of Sundays in that month:

=4+(DAY(M2-DAY(M2)+35)<WEEKDAY(M2-DAY(M2)))

....although you originally said that you would have a cell with the month in
text, so if M2 contains a text representation of the month e.g. Jan or
October then this formula will give you the number of Sundays in that month
in the current year:

=4+(DAY((1&M2)+34)<WEEKDAY((1&M2)+6))