Convert a Date to Text in Excel

This page describes the following three different methods, that can be used to convert a date to text in Excel.

Excel Convert Date to Text Index:
Date to Text By Adding an Apostrophe
Date to Text Using Text to Columns
Date to Text Using Excel Functions

Convert a Date to Text By Adding an Apostrophe

Excel Cell With Apostrophe Inserted Before a Date

The quickest way to convert an Excel date to text is to insert an apostrophe in front of the date (see rightabove).

The apostrophe will not be displayed in the cell, but it will tell Excel to treat the contents of the cell as text.


Convert a Date to Text Using Excel Text to Columns

If you want to convert an entire column of Excel dates to text, you might prefer to use the Excel Text to Columns command.

However, note that the Text to Columns command only works on one column of data at a time.

To use the Excel Text to Columns command to convert dates to text:

  1. Select the range of cell(s) that you want to convert (these must not span more than one column).
  2. Excel Text To Columns Option Button

    From the Data tab on the Excel ribbon, select the Text to Columns option (see rightabove).

    This will cause the Convert Text to Columns wizard to open up. Within this:

    • Make sure the Delimited option is selected and click Next >;
    • Make sure that none of the Delimiters are selected and then click Next > again;
    • You should now be offered a selection of Column Data Formats. Select Text and click the Finish button.

      Excel Text To Columns Wizard Step 3 with Text Data Format Selected

Convert a Date to Text Using the Excel Text Function

The Excel Text Function converts a supplied numeric value (including an Excel date or time) to text, using a user-specified format.

The syntax of the function is:

TEXT( value, format_text )

where,

The spreadsheet below shows examples of the Excel Text Function used to convert the date 01/01/2016 into text strings with various date formats:

 Formula:
 AB
101/01/2016=TEXT( A3, "mm/dd/yyyy" )
201/01/2016=TEXT( A3, "mm/dd/yy" )
301/01/2016=TEXT( A3, "mmm-dd-yyyy" )
401/01/2016=TEXT( A4, "mmmm dd yyyy" )
501/01/2016=TEXT( A5, "ddd, dd mmm yy" )
 Result:
 AB
101/01/201601/01/2016
201/01/201601/01/16
301/01/2016Jan-01-2016
401/01/2016January 01 2016
501/01/2016Fri, 01 Jan 16

Continue to the Convert Text To Date page  >>