help with formula and date and text oh my

Copper Contributor

Hello, I am trying to figure out how to create a formula in a cell that will include Text and a date that will change by 7 days. An example is I want the Cell to read:   Week of 12/5/2023 and the next cell to read Week of 12/12/23, and so on.  I have this formula:  ="week of: "&TEXT(DATE(2023,12,10),"mm-dd-yyyy") however I am not sure how to keep adding 7 days to it each time i copy it over so the weekly planner goes out through the year.   Thank you

2 Replies

@TuesdayTues 

You can use SEQUENCE to generate the dates. If there's something in your sheet that tells how many dates to add, this formula could be refined even more:

 

This example generates 10 dates

=LET(
    date_text, "Week of ",
    start, DATE(2023, 12, 5),
    dates, 10,
    date_text & TEXT(SEQUENCE(dates, , start, 7), "m/d/yyy")
)

 

@TuesdayTues 

Alternatively, you can calculate the dates with such a SEQUENCE function, but use a custom date format to prepend (or append, or otherwise intersperse) literal text:

"Week of "m/d/yyyy;;;@

(Because these cells still contain date values (intrinsically, numbers), Excel will by default right justify the displayed value. If you prefer, you can left justify the text as usual from Home | Alignment.)


This technique has the advantage that you can use the calculated dates for additional processing, such as calculations, comparisons, and lookups.


See the attached workbook.