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.

Young businesswoman eating lunch at desk
Excel's Text function lets you format dates any way you wish.
credit: Siri Stafford/Digital Vision/Getty Images

Step

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.

Step

Convert the date to "10-Jan-2014" format by copying and pasting this formula into cell B2: =TEXT(A2,"dd-mmm-yyyy")

Step

Change the date to "10-01-2014" by copying this formula into cell B3: =TEXT(A2,"dd-mm-yyyy")

Step

Convert the date to "10-Jan-14" by putting this formula into cell B4: =TEXT(A2,"dd-mmm-yy")

Step

Convert the date to "Jan-10-2014" by copying this formula into cell B5: =TEXT(A2,"mmm-dd-yyyy")

Step

Change the date to "Jan-10-14" format by copying and pasting this formula into cell B6: =TEXT(A2,"mmm-dd-yy")

Step

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."