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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
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