How to format a cell from date to week?
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
"nginhong" wrote in message
...
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
|