Returning a date format in a formula as date format (currently showing as text)

[divster27]

I have a simple formula that looks at cells to return a string of data - release date , Artist, Title

Release date is in column M, Artist in column B, title in column C

i want to come back with the following data

Release date = 12/03/2021 - Black Crowes - Southern Harmony
my formula looks like this

="RELEASE DATE = "&M60 &" - "&B60&" - "&C60

Excel is returning the date in text format so it comes out like this:

Release date = 44267 - Black Crowes - Southern Harmony

with 44267 being the text format of the date - i want it to return the date format.

any suggestions?

 

[jasonb75]

You need to use the TEXT function in the formula to preserve the date in the correct format.

="RELEASE DATE = "&TEXT(M60,"dd/mm/yyyy")&" - "&B60&" - "&C60

 

[Fluff]

How about

="RELEASE DATE = "&Text(M60,"dd/mm/yyyy") &" - "&B60&" - "&C60

 

[Toadstool]

Hi Divster27,

Try ="RELEASE DATE = "&TEXT(M60,"dd/mm/yy")&" - "&B60&" - "&C60

 

[divster27]

not sure i can mark all as the solution - but you're all right!

 

[Fluff]

Glad we could help & thanks for the feedback.

not sure i can mark all as the solution

You can't, so I'd suggest Jason's post as he was the first.

 

[jasonb75]

You can't, so I'd suggest Jason's post as he was the first.

Thanks, fluff, but I'd suggest @Toadstool's post for 2 undisclosed reasons

 

[Toadstool]

Thanks, fluff, but I'd suggest @Toadstool's post for 2 undisclosed reasons

"Undisclosed reasons"?
Is it because I didn't see the required date format so checked the release date of Southern Harmony and found it was 12 May 1992 so figured dd/mm/yyyy was more likely than mm/dd/yyyy as then at least the day would be correct?
Is it because this is one of my very, very few responses which didn't use AGGREGATE?

 

[jasonb75]

I shall neither confirm nor deny ?