How to convert date to week number

问题:

How can I convert 20110114 (YYYYMMDD) to week, eg WK02-11, in excel ?

Thanks.

回答1:

First convert the number in a date. Supposing your number is in A1 cell

=DATE(LEFT(A1;4); MID(A1;5;2); RIGHT(A1;2)

Then use WEEKNUM

=WEEKNUM(DATE(LEFT(A1;4); MID(A1;5;2); RIGHT(A1;2), 2)

(gives 3)

Then, if you want, you could embellish the result:

="WEEK-" & WEEKNUM(DATE(LEFT(A1;2); MID(A1;5;2); RIGHT(A1;2), 2) & "-" & LEFT(A1;4)