Excel dates (by week) in cells

Hi all,

Can anyone tell me how I can drag across cells to create a series of week dates? I.e. 1st Jan, 8th Jan, 15th Jan? (British format). I`ll be dragging cells into A1, A2, A3 etc.

Also.... I intend to concatenate the words: 'Week commencing' before the dates. Let me know if you think this will cause a problem with the date format?

Thanks so much

Yes, it will - why do you need it in every cell? Put it once in A1 and start your dates in A2.

Put the start date in A2.

In A3: =A2+7

Drag copy down.

Ok, so based on what you wrote, 1st, if you put in cell A1 1/1/2018 and in A2 1/8/2018 then drag down no formula is needed as Excel will recognize the 7 day jump.
And what you "want" will interfere with how dates work unless you are willing to use a helper column with the actual dates in it.
If you did what I or Ali described in column A you'll have dates to work with.
If you want "Week Commencing 1st Jan" etc. in another location this formula will work (pointed to column A)...

="Week Commencing "&DAY(A1)&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&" "&LOOKUP(MONTH(A1),{1,2,3,4,5,6,7,8,9,10,11,12;"Jan","Feb","Mar","Apr","May","June","July","Aug","Sept","Oct","Nov","Dec"})

But you won't be able to do any calculations from it.

Thanks both - I`ll take this on board and experiment. Many thanks!