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 interpreting the supplied date_text argument, the Datevalue function follows simple rules to interpret the year. These rules (which are the same as if you type a date directly into a cell) are:
These rules are illustrated in the examples below.
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.
The easiest way to do this is to select the cell(s) to be formatted and then select the Date cell formatting option from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):
The resulting formatted spreadsheet is shown on the rightabove.
For further details on formatting in Excel, see the Excel Formatting page.
See the Microsoft Office website for further information and examples of the Excel Datevalue function.
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.|