Concatenating date with a string in Excel

I have two cells in Excel. one has a string and the other one has a date. in the third cell I want to put the date and the string together. For example:

A1 = "This "
A2 = "03/03/1982"

I want A3 to be:

This 03/03/1982

when I try to put this in the A3 formula: = A1 & A2 it returns some funny numerical value for the date and does not give me the literal date.

Solution 1:

Don't know if it's the best way but I'd do this:

=A1 & TEXT(A2,"mm/dd/yyyy")

That should format your date into your desired string.

Edit: That funny number you saw is the number of days between December 31st 1899 and your date. That's how Excel stores dates.

Solution 2:

This is the numerical representation of the date. The thing you get when referring to dates from formulas like that.

You'll have to do:

= A1 & TEXT(A2, "mm/dd/yyyy")

The biggest problem here is that the format specifier is locale-dependent. It will not work/produce not what expected if the file is opened with a differently localized Excel.

Now, you could have a user-defined function:

public function AsDisplayed(byval c as range) as string
  AsDisplayed = c.Text
end function

and then

= A1 & AsDisplayed(A2)

But then there's a bug (feature?) in Excel because of which the .Text property is suddenly not available during certain stages of the computation cycle, and your formulas display #VALUE instead of what they should.

That is, it's bad either way.