Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 722
Alex Gordon Avatar asked Jun 13 '11 21:06

Alex Gordon


People also ask

Does CONCATENATE work with dates?

To Concatenate Dates in Excel, first, we need to convert the cells which contain Date into Text format using the TEXT function, and there we will choose the format of the date that we want to keep in text format. Insert CONCATENATE function in a cell and right after starting the TEXT function.

How do I combine date and data in Excel?

There is a very simple formula that can quickly help you combine date column and time column into one. Tip: You also can use this formula =A2+B2 and then format the result cells as date and time formatting.

How do I use text to date in Excel?

Convert Date to Text using Text to ColumnGo to Data –> Data Tools –> Text to Column. This would instantly convert the dates into text format. Note: There is a difference in the format of the dates in the two columns. While the original format had dd mmm, yyyy format, the result is dd-mm-yyyy.


1 Answers

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.

like image 85
ChrisO Avatar answered Sep 22 '22 02:09

ChrisO