Microsoft Office Tutorials and References
In Depth Information
Converting Text Dates to Dates
Figure 3.11 shows a column of text dates. Follow these steps to convert the text dates to
1. Insert a blank Column B by selecting cell B1. Select Home, Insert, Insert Sheet
Columns. Alternatively, you can use the Excel 2003 shortcut Alt+I+C.
2. In cell B2, enter the formula =DATEVALUE(A2) . Excel displays a number in the 40,000
range in cell B2. You are halfway to the result (see Figure 3.11). You still have to format
the result as a date.
3. Double-click the fill handle in the lower-right corner of cell B2. Excel copies the
formula from cell B2 down to your range of dates.
function can handle any
of the date formats in
The result of the
DATEVALUE function is
a serial number.
The fill handle is the square dot in the lower-right corner of the active cell indicator.
4. Select Column B2. On the Home tab, select the drop-down at the top of the Number
group and choose either Short Date or Long Date. Excel displays the numbers in
Column B as a date (see Figure 3.12). Alternatively, you can press Ctrl+1 and select any
date format from the Number tab.
5. To convert the live formulas in Column B to be static values, while the range of dates
in Column B is selected, press Ctrl+C to copy. Press Ctrl+V to paste. Press Ctrl to
open the Paste Options dialog. Press V to paste as values.
6. Delete the original column A.
If some of the dates appear as #######, you need to make the column wider. To do so, double-click the
border between the column B and column C headings.