Date format into Week number format

Re: Date format into Week number format

=+RIGHT(YEAR(A1),2)&WEEKNUM(A1)
..and something else, where A1 is your date.

Re: Date format into Week number format

The basic formula you want may be:

=RIGHT(YEAR(A1),2)&WEEKNUM(A1,2)&"."&WEEKDAY(A1,2)

However, the WEEKNUM function is not returning 36 weeks for 9-Sep-2010. Are you using the european week format where week 1 is when the first week where the majority of days fall in the new year?

If you require the eurpean format then that is somewhat more complex.

[EDIT]

If you require your week numbers to always be 2 digits (ie 9-Jan-2010 to show as 1001.6 and not 101.6) then:
=RIGHT(YEAR(A1),2)&TEXT(WEEKNUM(A1,2),"0#")&"."WEEKDAY(A1,2)

If you require your week numbers to be in compliance with ISO 8601 then I would need to know how you want to handle the 52/53 week issue since ISO 8601 requires a W (either W01 or W53) to qualify that.

Re: Date format into Week number format

The ampersand was missing. Here's the revised one

=RIGHT(YEAR(A1),2)&TEXT(WEEKNUM(A1,2),"0#")&"."&WEEKDAY(A1,2)

Anyway, you don't need to pass a period(.) to bring that in to your final result. You could always use the aid of the formatting using TEXT function

The formula would then look like this

=RIGHT(YEAR(A1),2)&TEXT(WEEKNUM(A1,2),"0#.")&WEEKDAY(A1,2)

Re: Date format into Week number format

Thanks for catching the typo and quite right I had not thought of including the period in the format.

Re: Date format into Week number format

If you are using ISO week numbers then the year might also be problematic. For example Sunday 3rd Jan 2010 would be the last day of week 53 of 2009 so presumably your output would be 0953.7 for that date, Jan 4th being 1001.1. For that output try this formula

=TEXT(A1+4-WEEKDAY(A1,2),"yy")&TEXT(INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4))/7)+2,"00.")&WEEKDAY(A1,2)

Re: Date format into Week number format

daddylonglegs

Quite interested by your formula for ISO weeks. Its somewhat more efficient than the one I dug up yesterday while researching about ISO numbers, which is listed by MS here.

Re: Date format into Week number format

Thanks Rob,

That's a daddylonglegs special. I've been promoting that version for some years now. I think it's the shortest and best version (but I would say that, wouldn't I.....?).

Incidentally it's based on a similar version which replicates WEEKNUM(A1,2), i.e.

=INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1),1,1))/7)+2

Re: Date format into Week number format

Well without derailing this thread too much longer:

It is strange that MS continues to give promenance to a formula that does not make optimum use of Excels own functions (ie WEEKDAY(X,2) as opposed to WEEKDAY(X)) and has 3 more function calls.