How to concatenate a date in MS-excel and receive a date (not a number)

Question

When I try to concatenate two cells in MS-excel in the formula I have the date as a integer number (eg: 04 april 2012 become 41006).

How can I display the date also in the formula cells?

Answer 1

This is the way Excel sees dates. To see them the way you want they have to be formatted and when alone in a cell that can be done by changing the cell's formatting. When you want to combine a date with other things, such as text, you need to use formula to format the date the way you want. Here's how:

Use the TEXT formula to change the date to text in the format you want. If you were concatenating the text Last run on with the date in a cell, say A2, then your formula would look like this

=CONCATENATE("Last run on ",TEXT(A2,"DD/MM/YYYY")) 

Of course you don't actually need the concatenate:

="Last run on "&TEXT(A2,"DD/MM/YYYY")

But the concatenate makes it clear what you're doing.

Either way these would return the value Last run on 06/11/2013 (if that was the date in question.) You can then change the text pattern to adjust the date format; e.g.

"MM-YY" for 06-11
"DD MMM" for 06 Nov
"DDDD DD MMMM YYYY" for Wednesday 06 November 2013