This Excel tutorial explains how to format the display of a date value in concatenated text in Excel 2003 and older versions (with screenshots and step-by-step instructions).
Question: In Microsoft Excel 2003/XP/2000/97, I have a date value stored in cell B2. I'd like to display in cell A4, a concatenated string with the date displayed as "dd/mm/yyyy". But when I concatenate the date value into the string, it shows as 39331 instead of "06/09/2007".
How can I get the date to properly display when I use it in this formula?
Answer: Let's look at an example.
As you can see, when you try to concatenate using a date value, Excel displays the serial representation of the date (ie: 39331) instead of the traditional format (ie: 06/09/2007).
To correct this, we need to use the Text function to convert the date to a text value. This allows us to select a date format to display.
In this example, we've used the Text function to apply the "dd/mm/yyyy" format to the value found in cell B2. This causes the date to appear as "06/09/2007" instead of 39331.
(scroll to see more)