How to Keep Dates as Text in Excelby Laurel Storm
By default, dates you type in Microsoft Excel spreadsheets are automatically converted into numbers; the program then applies special formatting to these numbers so they appear as dates. If you convert the cells that contain these dates to text format, however, all you will see are the numbers, giving no indication of the corresponding dates they represent. There are several methods you can use to prevent this from happening and keep dates readable in cells formatted as text. Two methods rely on entering the dates in a particular way, and the third can be used to convert dates you've already entered into text without needing to retype them one by one.
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.
- close Information in this article applies to Microsoft Excel 2013. It may vary slightly or significantly with other versions.
- photo_camera Sunday to Saturday monthly calendar, 2008 Year image by Stasys Eidiejus from Fotolia.com
Click here to provide feedback on this article