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|
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).
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:
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).
Click on the warning symbol, to bring up the Error Checking menu (see rightabove).or
to convert the cell values into dates.
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:
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:
From the Data tab on the Excel ribbon, select the Text to Columns option (see rightabove).
You should now be offered a selection of Column Data Formats. Select Date.
From the drop down menu alongside the Date option, select the format of the dates in your column (see rightabove).
Click the Finish button.
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.
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.
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):
The resulting spreadsheet, with the cells in column B formatted as dates, is shown below:
|3||01 Jan 2016||01/01/2016|