Count how many weeks in a range

[Fin Fang Foom]

I would like to count how many weeks in a range. Example look in column B there are 4 weeks in that range.

Book2
	A	B	C	D	E	F	G
1			Expected result			Expected result
2		9-01-2006	4		9-01-2006	1
3		9-11-2006			9-02-2006
4		9-18-2006			9-03-2006
5		9-28-2006			9-04-2006
6
7
8
9
Sheet1

 

[GorD]

Can you explain your definition of week.

Is it multiples of 7 days, or is a Sunday in a different week from a Saturday etc.

 

[Fin Fang Foom]

Thank You for replying GorD,



I use a helper column to solve my question. To bad there is not a all in one formula for this.

Thanks anyway.

Book1
	A	B	C	D	E	F	G	H	I
1				Expected result				Expected result
2		9-01-2006	35	4		9-01-2006	35	2
3		9-11-2006	37			9-02-2006	35
4		9-18-2006	38			9-03-2006	36
5		9-28-2006	39			9-04-2006	36
6
7
Sheet1

 

[barry houdini]

To bad there is not a all in one formula for this.

Try this formula in D2

=COUNT(1/FREQUENCY(INT((B2:B5-DATE(YEAR(B2:B5),1,1)-WEEKDAY(B2:B5))/7)+2,ROW(INDIRECT("1:53"))))

confirmed with CTRL+SHIFT+ENTER

 

[Fin Fang Foom]

To bad there is not a all in one formula for this.

Try this formula in D2

=COUNT(1/FREQUENCY(INT((B2:B5-DATE(YEAR(B2:B5),1,1)-WEEKDAY(B2:B5))/7)+2,ROW(INDIRECT("1:53"))))

confirmed with CTRL+SHIFT+ENTER

Perfect!! Thank you so much barry houdini!