How to Keep Dates as Text in Excel

When you convert dates into text format, Microsoft Excel automatically converts them into numbers, giving no indication of the corresponding dates they represent. To prevent this from happening, you can use an apostrophe, convert the cells to text before adding the dates, or use a formula to convert many cells at once.

Adding an Apostrophe

Select the cell in which you want to type a date. This method works best when you have only a few dates to enter.

Press the "apostrophe" key to insert an apostrophe as the first character of the cell, forcing Excel to keep the data formatted as text instead of converting it to a number.

Type the date and press "Enter." Although the apostrophe remains in the cell and you can see it in the formula bar, it is invisible in the cell itself.

Formatting Cells Before Typing

Select the cell or cells in which you want to type dates. This method works best when you have a large amount of dates to enter.

Press "Ctrl-1" to open the Format Cells dialog window. Click the "Number" tab, select "Text" from the list of available formats, and then click "OK."

Type the dates into the cells. Because the cells are already formatted as text, Excel will leave them as they are instead of converting them to numbers.

Converting Existing Dates in Bulk

Find a group of empty cells in your spreadsheet that you can use for the conversion. If the dates in your spreadsheet are clustered tightly together -- for example, if they are all in one column or all in one row -- this method can convert them all in one fell swoop, as long as you have as many empty cells as you have dates to convert. If the dates are scattered throughout your spreadsheet, the conversion process will take longer but the method remains efficient.

Type the following formula in your first empty cell, replacing "A1" with the first cell that contains a date you want to convert:

\=TEXT(A1, "dd mmmm yyyy")

Press "Enter." The formula converts the date in the first cell into text, using the format "01 January 1900." If you want the converted date to be in a different format, adjust the part of the formula within the quotes.

Select the cell in which you just typed the formula. Click and drag the small square at the bottom right corner of the cell to copy the formula to neighboring cells, converting dates contained in the cells near your original one as a result.

Press "Ctrl-C" to copy the converted dates. Right-click the first of the original cells, hover over "Paste Special" and select "Values" to paste the converted dates over the original ones, replacing them.

Select the group of cells you used to perform the conversion and delete them.