14

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?

26

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

  • 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 '14 at 14:55
  • Worked nicely for other formatting instructions, such as =CONCATENATE("It's ", TEXT(A2,"#.00"), "!") – RobertB Mar 11 '15 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. – Pasi Savolainen Oct 11 '16 at 11:54

protected by Community Oct 19 '17 at 13:35

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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