Formula

[sitelbanat]

Is there a formula that calculates how many full calendar weeks are in a month?

for example september 2006 = 4

 

[Datsmart]

Are you after only full calendar weeks? What about partile weeks at the beginning and end of the month?
Can you explain your request a little more clearly?

 

[sitelbanat]

Yes full calendar weeks

 

[Datsmart]

I don't know a simple formula to do this. I don't have time today to spend on this, but I would try using the WEEKNUM function from Analysis Toolpak and run some calculations against the date range of the month in question.

 

[gaj104]

Err don't all months have 4 weeks in them? Or am I missing something obvious?

Book1
	A	B	C	D
1	Date	Decimal weeks	No Weeks
2	01/01/2005	4.43	4.00
3	01/02/2005	4.00	4.00
4	01/03/2005	4.43	4.00
5	01/04/2005	4.29	4.00
6	01/05/2005	4.43	4.00
7	01/06/2005	4.29	4.00
8	01/07/2005	4.43	4.00
9	01/08/2005	4.43	4.00
10	01/09/2005	4.29	4.00
11	01/10/2005	4.43	4.00
12	01/11/2005	4.29	4.00
13	01/12/2005	4.43	4.00
Sheet1

 

[sitelbanat]

this is what im looking for thank you

 

[Datsmart]

August only has 3 FULL calendar weeks in it. The first and last weeks are short. That is what was requested in the posting.

 

[barry houdini]

Perhaps you already have your answer but if you want to count full weeks Sunday to Saturday, i.e. August 2006 has 3 but September 2006 has 4 then with any date from the month in question in A2

=INT((WEEKDAY(A2-DAY(A2))+DAY(DATE(YEAR(A2),MONTH(A2)+1,0)))/7)-1