Microsoft Excel: Converting Dates to Text

I ran into a problem the other day when I tried to upload a spreadsheet to a vendor's website. On our end, we had all of the dates in the spreadsheet as actual dates. The vendor needed the dates as text. Every time I tried to copy and paste the dates as text, they were rendered as a large number.

In order to work around the problem, you first have to understand the concept of displayed value verses actual value. In Excel, the displayed value is what you see in a particular cell and the actual value is what is in the formula. In the example below, we have the number 3 in cell C2.  However, the actual value is a formula adding together two other cells:

In Excel, dates are not actually dates. Instead they are a calculation of the number of days since Jan. 1, 1900. So when you type in 10/24/2012 into the cell, the actual value is 41,206 days.  So we have to figure out how to convert the date into text without it calculating this value. We can do this using the text formula:

=Text(Cell,Format)

In this formula, Cell refers to the cell you want to convert & format refers to how you want the cell formatted. In our case, we want to convert it to text, but keep the original slashes between the month, date and year. Here's how I accomplished it:

=Text(A1, "mm/dd/yyyy") 

The format part refers to month / day/ year. In this particular case, I want the year to display as 4 digits instead of 2 (i.e. 2012 vs 12). The end result was that my dates were converted to text but looked exactly the same as the original formula.