Microsoft Office Tutorials and References
In Depth Information
Converting a Date from Text
Excel would have no way of knowing whether this is January 24, 2015, or
the code for your combination lock. If it looks like a date, you can use the
DATEVALUE function to convert it into an Excel date value.
In practice, any standard date format entered into a cell is recognized by
Excel as a date and converted accordingly. However, there may be cases,
such as when text dates are imported from an external data source or data is
copied and pasted into Excel, that you need DATEVALUE.
Why not enter dates as text data? Because although they may look fine, you
can’t use them for any of Excel’s powerful date calculations without first
converting them to date values.
The DATEVALUE function recognizes almost all commonly used ways that
dates are written. Here are some ways that you may enter August 5, 2005:
DATEVALUE can convert these and several other date representations to a
date serial number.
After you’ve converted the dates to a date serial number, the dates can be
used in other date formulas or to perform calculations as described in other
parts of this chapter.
To use the DATEVALUE function, follow these steps:
1. Select the cell where you want the date serial number located.
2. Enter =DATEVALUE( to begin the function entry.
4. Type a ), and press Enter.
The result will be a date serial number, unless the cell where the result
is displayed has already been set to a date format.
Figure 12-7 shows how some nonstandard dates in Column A have been
converted to serial numbers with the DATEVALUE function in column B. Then
column C displays these serial numbers formatted as dates.
Did you notice something funny in Figure 12-7? Normally, you won’t be able to
enter a value such as the one in cell A4 — 02-28-10 — without losing the
leading 0. The cells in Column A had been changed to the Text format. This format