17

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?

1 Answer 1

32

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

3
  • 1
    I came across this answer when trying to concat cells together with a separator (as per this question) and this works for that, as well.
    – Mitch
    Nov 25, 2014 at 14:55
  • Worked nicely for other formatting instructions, such as =CONCATENATE("It's ", TEXT(A2,"#.00"), "!")
    – RobertB
    Mar 11, 2015 at 22:48
  • One note, if the sheet is in non-english locale, then the formatting codes are also localized, so for example in Finnish you would do "vvvv-kk-pp" as the formatting code. Oct 11, 2016 at 11:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.