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

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

"SL" wrote in message
...
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

Thank You - just the ticket

"Don Guillett" wrote:

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

"SL" wrote in message
...
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

When won't it be 4?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SL" wrote in message
...
Thank You - just the ticket

"Don Guillett" wrote:

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

"SL" wrote in message
...
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

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

"Don Guillett" wrote:

When won't it be 4?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SL" wrote in message
...
Thank You - just the ticket

"Don Guillett" wrote:

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

"SL" wrote in message
...
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

"SL" wrote:

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)

"Don Guillett" wrote:

When won't it be 4?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SL" wrote in message
...
Thank You - just the ticket

"Don Guillett" wrote:

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

"SL" wrote in message
...
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

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))





"SL" wrote:



"SL" wrote:

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)

"Don Guillett" wrote:

When won't it be 4?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SL" wrote in message
...
Thank You - just the ticket

"Don Guillett" wrote:

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

"SL" wrote in message
...
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