Microsoft Excel 2007 tutorial: convert text dates to dates

Convert text dates to dates

VII. Convert text dates to dates

Sometimes dates may be formatted or stored in cells as text. For example, you may have typed a date in cell that was formatted as text or you might import or copy dates from external source as text.

Dates that are stored as text are left-aligned rather than right-aligned.

Excel 2007 text dates

With Error Checking turned on, you will see text dates with two-digit years might be marked with an error indicator Excel 2007 error indicator.

Excel 2007 text dates with 2 digit years

In Microsoft Excel, Error Checking can identify text-formatted dates with two-digit years, so you use the automatic correction option to convert them to date-formatted dates. To convert most other types of text dates to dates, you can use the DATEVALUE function.

VII.1. Convert text dates to dates with 2 digit years by using Error Checking

- Open Microsoft Office Excel 2007, click Office Button, and then click Excel Options button.

Excel 2007 Excel Options

- On the Excel Options dialog box, click Formula tab, and then follow all steps below:

   1. Under Error Checking, make certain that the Enable background error checking check box is checked.

   Excel 2007 Error Checking

   2. Under Error Checking Rules, make certain that the Cell containing years represented as 2 digits check box is selected.

  Excel 2007 two-digit years

   3. Click Ok button. 

- Select the cell or range of cells that has an error indicator in the upper-left corner Excel 2007 error indicator . Note that all cells that you select must be adjacent cells.

- Next to the selected cell or range of cells, click the error button that appears Excel 2007 Error button, and then click Convert XX to 19XX or Convert XX to 20XX.

Excel 2007 convert text dates to dates

VII.2. Convert text dates to dates by using the DATEVALUE function

By using the DATEVALUE function, you can convert a text date to a number that represents the date in Microsoft Office Excel date-time code. And then you need to copy the formula and use Paste Special to apply date format to the selected cells that contain the text dates.

- On the worksheet, select a blank cell and format it as General.

  To format cell, do the following:

   ▫ On the Home tab, in the Number group, click drop-down arrow of the Number Format box, and then click General. Or you can click Dialog Box Launcher next Number, and then click General in the Category box.

   Excel 2007 Arrow with Dialog Box Launcher 

- Click the blank cell, then type =DATEVLUE( the cell that contains the text date which you want to convert), and then press Enter. You will see the DATEVALUE function returns the number that represents the text-formatted date.

- If text dates are in a range of cells, you need to copy the conversion formula to those cells by using the fill handle Excel 2007 the fill handle. Click the cell in which you typed the formula, and then drag the fill handle across a range of empty cells that matches in size the range of cells that contain text-formatted dates.

After the fill handle is dragged, you have a range of cells with numbers that corresponds to the range of cells that contain text-formatted dates. See the figure below.

Excel 2007 range of cells

- Select the cell or range of cells that contains the numbers, and then on the Home tab, in the Clipboard group, click Copy.

Excel 2007 Clipboard group

Or you can use keyboard shortcut by pressing Ctrl +C.

- Select the cell or ranges of cells that contains text dates which you want to convert.

- On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.

Excel 2007 Paste Special option

- In the Paste Special dialog box, under Paste, select Values, and then click Ok button.

Excel 2007 select values under paste

- On the Home tab, in the Number group, click drop-down arrow of the Number Format box, and then select Short Date or Long Date that you want.


- If you want other date formats, click More Number Formats in the Number Format box. In the Format Cells dialog box, on the Number tab, in the Category box, click Date, and in the Type list, click the date format that you want. Click Ok button eventually to apply this format.

- After you converted all of the text dates successfully, you may see the cells that contain numbers that represent dates as the figure below:

Excel 2007 error value

To delete them, select these cell, and then press Delete.



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.