Excel Tip: Convert Serial Number Date to Text

People make much to do of converting a serial number (25556) to a text value (12/19/1969) in Excel. After all, it's easy to display the serial number as a date in Excel using Cell Formating. However, if you need to export the date value for use outside of Excel, the serial number format is of no use. Here's a simple way (no coding required) to convert serial number formating to a text value.

In this example, column "A" has the serial number dates. It doesn't matter how they are formatted.

1. Insert two blank columns to the right of column "A"
2. Paste the following formula into the first new column (column "B" in this example)
   =CONCATENATE(MONTH(A1),"/",DAY(A1),"/",YEAR(A1))
   NOTE: Be sure to use relative links (A2 for row 2, A3 for row 3, etc.)
3. Select column "B" and select Edit>Copy
4. Select column "C" and select Edit>Paste Special with the "Values Only" option

You're done. Column "A" contains a date serial number, column "B" contains a formula but displays a date, and column "C" has a text value for the date.