Forum Discussion

TuesdayTues's avatar
TuesdayTues
Copper Contributor
Dec 06, 2023

help with formula and date and text oh my

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

excel
Formulas and Functions
  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Dec 07, 2023

    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.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Dec 06, 2023

    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")
)

     

Resources