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, for which you need DATEVALUE.
Why not enter dates as text data? 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.
Excel’s DATEVALUE function recognizes almost all commonly used ways that dates are written. Here are some ways that you may enter August 14, 2018:- 8/14/18
- 14-Aug-2018
- 2018/08/14
After you’ve converted the dates to a date serial number, you can use the dates in other date formulas or perform calculations with them.
To use Excel’s DATEVALUE function, follow these steps:
- Select the cell where you want the date serial number located.
- Type =DATEVALUE( to begin the function entry.
- Click the cell that has the text format date.
- Type a ) and press Enter.
The result is a date serial number unless the cell where the result is displayed has already been set to a date format.
Converting dates to their serial equivalents with the DATEVALUE function.
Do you notice something funny in the image above? Normally, you aren’t 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 tells Excel to leave your entry as is. The Text format is one of the choices in the Category list in the Format Cells dialog box.
Note also that the text date in cell A8, Feb 9 14, could not be converted by DATEVALUE, so the function returns the error message #VALUE#. Excel is great at recognizing dates, but it’s not perfect! In cases such as this, you have to format the date another way so DATEVALUE can recognize it.