Weeknumber

Is there any way of calculating the weeks number (1-52) for a series of dates IF you don't have the Analysis Toolpak VBA installed to use the =WEEKNUM formula???

Originally Posted by loscherland

Is there any way of calculating the weeks number (1-52) for a series of dates IF you don't have the Analysis Toolpak VBA installed to use the =WEEKNUM formula???

Try the following...

Week beginning on Sunday:

=MATCH(A1,DATE(2005,1,1)-WEEKDAY(DATE(2005,1,1),3)-1+(ROW(INDIRECT("1:53")))*7-7)

Week beginning on Monday:

=MATCH(A1,DATE(2005,1,1)-WEEKDAY(DATE(2005,1,1),3)+(ROW(INDIRECT("1:53")))*7-7)

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Originally Posted by Domenic

Try the following...

Week beginning on Sunday:

=MATCH(A1,DATE(2005,1,1)-WEEKDAY(DATE(2005,1,1),3)-1+(ROW(INDIRECT("1:53")))*7-7)

Week beginning on Monday:

=MATCH(A1,DATE(2005,1,1)-WEEKDAY(DATE(2005,1,1),3)+(ROW(INDIRECT("1:53")))*7-7)

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Correction...

Week beginning on Sunday:

=MATCH(A1,IF(WEEKDAY(DATE(YEAR(A1),1,1),2)=7,DATE(YEAR(A1),1,1)+(ROW(INDIRECT("1:53")))*7-7,DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1),3)-1+(ROW(INDIRECT("1:53")))*7-7))

Week beginning on Monday:

=MATCH(A1,DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1),3)+(ROW(INDIRECT("1:53")))*7-7)

...where A1 contains the date of interest. Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Originally Posted by Domenic

Correction...

Week beginning on Sunday:

=MATCH(A1,IF(WEEKDAY(DATE(YEAR(A1),1,1),2)=7,DATE(YEAR(A1),1,1)+(ROW(INDIRECT("1:53")))*7-7,DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1),3)-1+(ROW(INDIRECT("1:53")))*7-7))

Week beginning on Monday:

=MATCH(A1,DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1),3)+(ROW(INDIRECT("1:53")))*7-7)

...where A1 contains the date of interest. Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

FWIW, I've modified the first formula...

=MATCH(A1,DATE(YEAR(A1),1,1)-IF(WEEKDAY(DATE(YEAR(A1),1,1),2)<7,WEEKDAY(DATE(YEAR(A1),1,1),3)+1)+(ROW(INDIRECT("1:53")))*7-7)

...confirmed with CONTROL+SHIFT+ENTER.