Setting up and invoice system and i'm trying to make as much of is automatic as possible. I've set it up with a week ending date/formula that auto fills the days of the week Monday thru Sunday.

My goal now is one cell in particular needs to read, "Week of: Date(from cell F6) Thru Date(from cell L6)

Can anybody help with the formula?

Dates are Week Ending (H4) F6 G6 H6 I6 J6 K6 L6 Monday (G6-1) Tuesday (H6-1) Wednesday (I6-1) Thursday (J6-1) Friday (K6-1) Saturday (L6-1) Sunday (=H4)

  • This is what my formula looks like but returns a #Name error ="Week of:"&TEXT(F6,mm/dd/yy)&""&TEXT(Thru,"TRUE")&TEXT(L6,mm/dd/yy)
    – Tom
    Jan 17, 2022 at 16:49

1 Answer 1



="Week of: " & TEXT(F6,"mm/dd/yy") & " Thru " & TEXT(L6,"mm/dd/yy")

The formatting string needs to be wrapped in double-quotes. I think you also manages to get the quote marks around the concatenated text in the wrong place. Also, unless you need the separate day columns you could just do

="Week of: " & TEXT((H6-7),"mm/dd/yy") & " Thru " & TEXT((H6-1),"mm/dd/yy")
  • Thank you that worked perfectly. I'm relatively new to this so missed the quotes
    – Tom
    Jan 17, 2022 at 19:03

You must log in to answer this question.