Microsoft Office Tutorials and References
In Depth Information
Converting Text Dates to Dates
Selecting a new format from the Format Cells dialog does not fix this problem, but it may prevent you
from fixing the problem! If you import data from a .txt file and choose to format that column as text,
Excel changes the numeric format for the range to be text. After a range is formatted as text, you can
never enter a formula, number, or date in the range. People try to select the range, change the format
from text to numeric or date, and hope this will fix the problem—but it doesn’t. After you change the
format, you still have to use a method described in the “Converting Text Dates to Real Dates” section,
later in this chapter, to convert the text dates to numeric dates.
However, it is still worth changing the format from a text format to General, Date, or anything else. If
you do not change the format, and then insert a new column to the right of the bad dates, the new
column inherits the text setting from the date column. This causes your new formula (the formula to
convert text to dates) to fail. Therefore, even though it doesn’t solve your current problem, you should
select the range, click the Dialog Launcher icon in the lower-right corner of the Number group on the
Home tab, and change the format from Text to General. Figure 3.9 shows the Dialog Launcher icon.
Many groups on the
ribbon have this tiny More
icon in the lower-right
corner. Clicking this icon
leads to the legacy dialog
b o x .
Excel for Windows, which needed to be compatible with Lotus 1-2-3, adopted the 1900
convention. As demonstrated in the next case study, the 1900 convention incorrectly made
1900 a leap year.