Excel - date range column heading

Question

Given a date, say 01/16/2017, how do I display column-1 heading as "01/16 - 01/22" ; column-2 heading as "01/23 - 01/29" and so on in MS-Excel-2013 - should fill column heading dynamically depending on the given date - Column heading - mm/dd - day (of the given date) to that Sunday and Mon-Sun for the following columns. - Need to fill about 20 columns Thank you.

Answer 1

I think this is what you need:

Assuming the date of reference is located in the cell A1,

=CONCATENATE(TEXT($A$1,"mm/dd"), " - ",TEXT($A$1+6,"mm/dd"))

this will give you a header "01/16 - 01/22" if the given date is "01/16/2017"

=CONCATENATE(TEXT($A$1+7,"mm/dd"), " - ",TEXT($A$1+13,"mm/dd"))

this will give you a header "01/23 - 01/29" for the same given date

I would like just to note that this formula is quite sensible to your regional settings, in Germany for instance mm/dd would become MM/TT

Answer 2

=TEXT($A$1+7*(COLUMNS($A$1:A1)-1) & "mm/dd") & " - " & TEXT($A$1+7*(COLUMNS($A$1:A1)-1)+6,"mm/dd")

Either of these

• COLUMNS($A$1:A1)-1
• ROWS($A$1:A1)-1

... will give you an increasing number for either COLUMNS or ROWS. The dual reference to A1 could be anywhere (e.g. on the current sheet) as long as it doesn't step out of the sheet when you copy the formula.

I believe the rest of the formula is pretty "standard".