format date cell with week number

hello
just wondering if its possible to format a cell to display date and week number
and if so how to go about it

eg

04/01/09 week 1

AFAIK -- no, you can't format a date value to show the week number via a Custom Format.

You could use another cell referencing said date value:

=TEXT(A1,"dd/mm/yy")&" Week "&WEEKNUM(A1,2)

Where A1 holds date 4th Jan 2009

WEEKNUM by default would use Sunday as start of week, by setting return type to 2 Monday becomes start of the week, ie the above returns Week1 whereas:

=TEXT(A1,"dd/mm/yy")&" Week "&WEEKNUM(A1)

would return Week 2.

OK glad you have resolved - in Excel WEEK 1 commences from 1st Jan regardless of weekday of 1st Jan... so normally this is the issue for people... ie 1st Jan 2016 is a Friday so it's likely that with your data Week 1 commences on the 4th Jan... ie XL 1 too high.

I will try and digest Chip Pearson's formula myself...

Ah, ok, well it's pretty simple in truth just replace my formula with the one you found:

=TEXT(A1,"dd/mm/yy")&" Week "&INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

Revision in red.