How to Convert the Date to Text in Excel
Excel spreadsheets often contain dates in a numeric format. For a variety of reasons, which may include presentation, sorting or other functions such as "Lookup," it may be desirable for the date to be stored in text format. There are a variety of formulas that you can use to convert the dates in your Excel spreadsheets to text format. These formulas can be easily customized to produce the date layout that you prefer.
Open in Excel the workbook that has the dates you want converted to text. Otherwise, you can practice in a blank worksheet by typing a date, like "1/10/2014" in cell A2. The converted date text can go in column B. If you are using an existing worksheet replace cell "A2" in these exeamples with the cell where your date is located.
Convert the date to "10-Jan-2014" format by copying and pasting this formula into cell B2: =TEXT(A2,"dd-mmm-yyyy")
Change the date to "10-01-2014" by copying this formula into cell B3: =TEXT(A2,"dd-mm-yyyy")
Convert the date to "10-Jan-14" by putting this formula into cell B4: =TEXT(A2,"dd-mmm-yy")
Convert the date to "Jan-10-2014" by copying this formula into cell B5: =TEXT(A2,"mmm-dd-yyyy")
Change the date to "Jan-10-14" format by copying and pasting this formula into cell B6: =TEXT(A2,"mmm-dd-yy")
Create your own date text format in cell B7, using any variation of months days and years from these examples, such as "yyyy-mmm-dd" or "yy-dd-mmm."
Tips & Warnings
- Unless you use the Paste Special options, copying and pasting date-text cells will paste the formula, not the text. To copy and paste the text values as you see them, copy the cell first and then click the cell where you want to paste it. Press "Ctrl-Alt-V" to open the Paste Special dialog box. Select the "Values" option and click "OK."
- Information in this article applies to Excel 2013. It may vary slightly or significantly with other versions or products.