As Excel dates are stored internally as numeric values (serial numbers), Excel doesn't generally recognise text representations of dates as actual dates.
Therefore the Datevalue function is provided to convert text representations of dates into serial numbers that Excel can recognise as dates.For more information on dates & times in Excel, see the Excel Dates & Times page.
The Excel Datevalue function converts a text representation of a date into an Excel date.
I.e. the function converts a text string representing a date, into the serial number that represents the date in Excel's date-time code.
The syntax of the Datevalue function is:
where the date_text argument is a text string representing a date.
When reading in the supplied date_text argument, the Datevalue function follows simple rules to interpret the date's year. These rules (which are the same as if you type a date directly into a cell) are:
In the following spreadsheet, the Excel Datevalue function is used to return a date serial number for five different text representations of dates:
In the above example spreadsheet:
If the results of your Datevalue function are displayed as a simple number (as in the example above), and you want Excel to display the results as dates, this can be done by formatting the cells to have the date type. To do this:
The resulting formatted spreadsheet is shown on the rightabove.
Further information and examples of the Excel Datevalue function can be found on the Microsoft Office website.
If you get an error from the Excel Datevalue function, this is likely to be the #VALUE! error:
|#VALUE!||-||Occurs if the supplied date_text cannot be recognised as a valid Excel date.|