How to format a cell from date to week?

[nginhong]

Dear all,

How to format a cell date like "2009-12-10" to year /week format "YYYYWW" =
200950?
Also how to determine number of weeks between 2 weeks like from 200950 to
201002 = 5 weeks?

BR//nginhong

 

[Fred Smith]

You cannot achieve this with cell formatting. Excel does not support weeks
in a date format.

To change a date to yyyyww, use:
=year(a1)*100+weeknum(a1)

The number of weeks difference between two of these cells, use:
=(int(a2/100)-int(a1/100))*52+(mod(a2,100)-mod(a1,100))

This calculates to 4 weeks, not 5, just as the difference between 201001 and
201002 is 1, not 2. If you really want 5 weeks as the answer, add 1 to the
above.

Regards
Fred

 

[Teethless mama]

Ans 1:
=YEAR(A1)&WEEKNUM(A1)

Ans 2:
Assume A3 holds: 200950, and A4 holds 201002

=(LEFT(A4,4)-LEFT(A3,4))*53+RIGHT(A4,2)-RIGHT(A3,2)