---

MS Excel 2003: Format the display of a date value in concatenated text

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.