How do you calculate the number of weeks on a month

[Guest]

Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a month,
using a date inputted in cell F2 - usually the 1st of the month.

Thanks in advance.

 

[Bob Phillips]

What is the criteria for a week? Does November have 4 or 5, and how do you
determine?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

November would have 5, December would be 6, January 5

 

[Bob Phillips]

And why as I said earlier is that the case, what are the criteria?

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

A criteria for a week is from Sunday to Saturday. So if the 1st is on a
Saturday it is still one week, if the 31st is on a Sunday it is also still
one week.

I hope this makes clearer sense?

Thanks

 

[Bob Phillips]

Thanks, got it now

=4+(DAY(F2-DAY(F2)+35)<WEEKDAY(F2-DAY(F2)-1))+(WEEKDAY(F2)<>1)

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I would never had gotten that right.

Works great, thanks.