Weeknum

[Chaz1945]

Was given following formula which worked fine until the year changed -
=WEEKNUM(A2)-WEEKNUM(DATE(2005,3,1))+1

My spreadsheet runs timestamps from 1 mar 2005 thru to 1 mar 2007. 1 Mar 2005 was week 1 and worked great thru to 31 dec 2005 which was week 44.
As soon as the date changed to 1 Jan 2006 - formula returned - 8 !!!!
I have tried fiddling around but still have no success in January 2006 carrying on from 2005 increasing from 44 onwards.
Spreadsheet shows

Date (A2 etc) ........ Week number
01/03/2005 ...................1
thru to
31/12/2005 ...................44

then !
01/01/2006 .................. -8

Would appreciate any help again - thanks in advance
Chaz

[daddylonglegs]

See my reply to your original thread.....

http://www.excelforum.com/showthread...50#post1760550

...if you want to start again at week 1 every 1st March then try

=INT((INT(A2)-DATE(YEAR(EDATE(A2,-2)),3,1))/7)+1

of course this will mean that you'll have a week 53 every year that lasts for only 1 day (or 2 days in a leap year)

[Chaz1945]

Yes, got that now !! Works fine - thanks a lot for your assistance.

Excellent forum - great guys and gals helping out.