Concatenation with dates - I want the date not numbers 2

[gtarrant]

Hi everyone.

I have a problem that one of you could help me solve. I want to concatenate a string with a date - simple, right?

Here's what I get:

Column A1 Column A2
7/8/2004 The date is 38176

Here's my formula: =CONCATENATE("The date is ",A1)

Why can't excel simply return the date in date format? I need to be able to convert this within the formula without having to format columns on the spreadsheet.

 

[RoyVidar]

Try using the Text function, specifying the format, for instance:

[tt]=CONCATENATE("The date is ",text(A1,"mm/dd/yyyy"))[/tt]

Roy-Vidar

 

[gtarrant]

You rock Roy! Thanks for the very quit response...it's exactly what I needed!

 

[Molby]

Whoops, tripped over the star that gtarrant left lying on the floor Roy, better pick it up for you...