Calculating week number without WEEKNUM

[TommyMc]

We have a fiscal calendar which starts Oct 1. I would like to display the proper week numbers. I worked out a formula which seems to work (except for week 53) but it would be better if I didn't have to rely on other users having the Analysis Toolpak installed. My date is located in '3930!I4' and this is the formula that works with the toolpak:
=IF(WEEKNUM('3930'!I4)>=40,WEEKNUM('3930'!I4)-39,WEEKNUM('3930'!I4)+13)

I searched and found an alternate formula to replace WEEKNUM:
=INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1
but when I substitute it for WEEKNUM in my formula, it doesn't work:
=IF(INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)>=40,INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)-40,INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)+14)
Returns the number 39723 rather than (week) 1
Can anybody help adjust my formula? Thanks

 

[barry houdini]

It works for any date. Obviously it will only match your requirements if your company defines week numbers the way I described, i.e. with week 1 starting on the first Sunday on or after 28th December

 

[ntufwc5]

That works a treat thanks, will this still be applicable after this year or will it go out of sync again? ie after week 53 in 2009?

Not sure if this help :

=TRUNC(((E2-DATE(YEAR(E2),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(E2),1,1))>WEEKDAY(E2),1,0)