Using the Day of the Week, and the Month, in a String

[DavidWF]

I know this dates me somewhat but I'm using Excel 2007.
I'm trying to create a section of text within an Excel worksheet that begins with a string with the format: "On Tuesday the 21st of January, 2025 . . . . "
The actual date is being picked up from a matrix using VLOOKUP and is stored in cell Y5 on the worksheet. Y6 contains the '21st' and Y7 is intended to contain the month.
I can get '21st' OK by taking the date from the matrix and using a Lookup List to add the 'st', and I can get 2025 OK, but I cannot figure out how to get the text version of the weekday or the month. The string is currently: =CONCATENATE("On ",Y5," the ",Y6," day of ",Y7)
The result I keep getting is: "On 45678 the 21st day of 45678".

Is what I'm trying to achieve practicable, and if so, how?

 

[saboh12617]

Hello,

I would have recommanded you the TEXT function - Microsoft Support or DAY function - Microsoft Support, but according to the website they are not available on Excel <2016... Is that really a thing?

 

[Fluff]

I'm using Excel 2007.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=CONCATENATE("On ",TEXT(Y5,"dddd")," the ",Y6," day of ",TEXT(Y5,"mmmm, yyyy"))

 

[Scott Huish]

If you can do without the nd, st, and th, perhaps just this, since Y5 contains the complete date.

=TEXT(Y5,"""On"" dddd ""the"" d ""day"" of mmmm yyyy")

 

[DavidWF]

My thanks to each of you - the use of TEXT was certainly the answer and has been stored away in the memory cells for future use in a similar situation.

@Fluff - my profile has been updated as recommended.

@Scott - yes, that would have been a more elegant solution but I'm creating a semi-automated version of a standard document so I'm constrained by the existing usage format and the nd, st, rd etc has to stay there. The use of multiple quote marks (") was interesting though; I'd not seen or used that before, so that's another one that's been stored away for what will almost certainly be future use.

 

[Fluff]

Glad we could help & thanks for the feedback.

Thanks also for updating your profile.