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.
With Error Checking turned on, you will see text dates with two-digit years might be marked with an error indicator .
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.
- Open Microsoft Office Excel 2007, click Office Button, and then click Excel Options button.
- 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.
2. Under Error Checking Rules, make certain that the Cell containing years represented as 2 digits check box is selected.
3. Click Ok button.
- Select the cell or range of cells that has an error indicator in the upper-left corner . 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 , and then click Convert XX to 19XX or Convert XX to 20XX.
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.
- 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 . 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.
- Select the cell or range of cells that contains the numbers, and then on the Home tab, in the Clipboard group, click Copy.
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.
- In the Paste Special dialog box, under Paste, select Values, and then click Ok button.
- 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:
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: