Home » Basic-Excel » Convert-Text-To-Date

Convert Text to a Date in Excel

If your spreadsheet contains text representations of dates, rather than actual dates (stored internally as integers by Excel), this may produce errors when you try to use these dates in calculations. Therefore, this page describes three different methods that you can use to convert text to a date in Excel.

Excel Convert Text to Date Index:
Text to Date Using Error Checking
Text to Date Using Text to Columns
Text to Date Using Excel Functions

Convert Text to a Date Using Excel Error Checking

Excel has an error checking option that can alert you to the presence of cells containing text representations of dates with two-digit years. This will be seen as a small colored triangle (the error indicator) in the top left corner of your cells (see below).

Error Indicator in Cells Containing Dates Stored as Text

If your cells display this error indicator, you can use Excel error checking to convert text representations of dates into actual numeric dates.

To do this:

  1. Select the cell (or cells) containing the values that you want to convert.

    This will cause a warning symbol to pop up at the side of the cell(s). If you then hover over the warning symbol, a warning message is displayed (see below).

    Error Checking Warning Message Informing of Date String with Two Digit Year
  2. Error Checking Menu for Text Date with Two Digit Year

    Click on the warning symbol, to bring up the Error Checking menu (see rightabove).

    Select one of the options

    Convert XX to 19XX

    or

    Convert XX to 20XX

    to convert the cell values into dates.

Error Checking Option

In order for the above method to work, you need to ensure that you have the Excel Error Checking option for cells containing years represented as 2 digits enabled.

To access this option:

  1. Click on the File tab (or the Microsoft Office Button in Excel 2007) on the Excel ribbon, and select Options.
  2. Within the Excel Options window, select Formulas.
  3. Within the Error Checking section, make sure the option Enable background error checking is checked.
  4. Within the Error checking rules section, make sure the option Cells containing years represented as 2 digits is checked.
  5. Click OK.

Convert Text to a Date Using Excel Text to Columns

The Excel Text to Columns command will also convert Excel text to dates. This method has the advantage that it can recognise several different date formats. However, the Text to Columns command will only work on one column at a time.

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

  1. Select the range of cell(s) that you want to convert (this 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 pop 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 Date.

      Excel Text To Columns Wizard Step 3 with Date Data Format Selected
      Drop Down Menu Showing Different Date Formats in Excel Text To Columns
    • From the drop down menu alongside the Date option, select the format that applies to the dates in your spreadsheet (see rightabove).
    • Click the Finish button.


Convert Text to a Date Using the Excel Datevalue Function

The Excel Datevalue function converts a text representation of a date into an Excel date serial number.

Note that, after using the function to convert a text string into a date, you need to make sure that the cell containing the function is formatted as a date.

Datevalue Function Examples

Cells A1-A3 of the spreadsheets below contain three different text representations of the date 01 January 2016.

The Excel Datevalue function is used in column B of the spreadsheet, to convert these text values into date serial numbers.

 Formulas:
  A B
1 01/01/2016 =DATEVALUE( A1 )
2 01/01/16 =DATEVALUE( A2 )
3 01 Jan 2016 =DATEVALUE( A3 )
 Results:
  A B
1 01/01/2016 42370
2 01/01/16 42370
3 01 Jan 2016 42370

In the above 'results' spreadsheet, column B is formatted with the 'General' format type. Therefore, the date serial number 42370 is displayed as an integer.

If you want to display the contents of column B as dates, you need to change the formatting of these cells to have a date format.

The easiest way to do this is to select the cell(s) to be formatted and then select the Date cell formatting option from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):

Excel Format Cells Drop Down Menu on Ribbon With Date Option Selected

The resulting spreadsheet, with the cells in column B formatted as dates, is shown below:

Results spreadsheet with date formatting applied to column B:
  A B
1 01/01/2016 01/01/2016
2 01/01/16 01/01/2016
3 01 Jan 2016 01/01/2016

Further methods of formatting dates in Excel are discussed on the Excel Date Format page.

Continue to the Convert Date To Text page  >>

Return to the Basic Excel Page
Return to the ExcelFunctions.net Home Page