Generate calendar weeks in cells

Hi!

I want to generate a succesion of calendar weeks in a row. Something like this:

"CW 16 (11.04 - 15.04)" "CW 17 18.04 - 22.04" "CW 25.04 - 29.04" ...

So: CW "the number of week" ("the date of the monday of the week" - "the date of the friday of the week")

Can I do this without a macro?

I get the results you're looking for with the following:

="CW " & COLUMN()+ 15 & " " & TEXT(DATE(2016,1,-3)+(COLUMN()+14)*7,"dd.mm") & " - " & TEXT(DATE(2016,1,1)+(COLUMN()+14)*7,"dd.mm")

Put the formula in A1 and fill right. If your data doesn't start in column one, you'll need to adjust the "COLUMN + X" portion of the formula to match. "COLUMN + 15" should equal your desired week number and "COLUMN + 14" should be one less.

cantosh's formula's use of TEXT is a good insight. If we apply that in my formula it gets much shorter. Note that cantosh's formula is specific to 2016 whereas my formula adapts to whatever is the current year. It is not clear what your needs are.

="CW "&COLUMN()&" ("&TEXT(DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1),12))+7*(COLUMN()-1),"mm.dd")&" - "&TEXT(DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1),12))+7*(COLUMN()-1)+4,"mm.dd")&")"

Thank you very much for your input! I needed the formular for a planning tool and only for the year 2016. 6StringJazzer thank you very much for your alternative as well!