Excel DATEVALUE Function
Excel Dates
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.Function Description
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:
DATEVALUE( date_text )
where the date_text argument is a text string representing a date.
Year Interpretation
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:
- If just a date and month are provided, the returned date will have the current year.
- The default settings for the year are that one- and two-digit years are interpreted as follows:
- The numbers 0 through to 29 are interpreted as the years 2000 to 2029;
- The numbers 30 through to 99 are interpreted as the years 1930 to 1999.
These rules are illustrated in the examples below.
Datevalue Function Examples
In the following spreadsheet, the Excel Datevalue function is used to return a date serial number for five different text representations of dates:
|
Formulas:
|
Results:
|
In the above example spreadsheet:
- The text string in cell A3 contains just a day and month, and so the function returns a date in the current year. As these examples were input into Excel in the year 2016, the returned date has the year 2016.
- The cells in the 'Results' spreadsheet all have the 'General' formatting type and so display Excel's underlying numeric value for the resulting dates.
Formatting Cells to Display Dates
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):
Results with date formatting:
| A | |
|---|---|
| 1 | 01/01/2016 |
| 2 | 01/01/2016 |
| 3 | 01/01/2016 |
| 4 | 01/01/2029 |
| 5 | 01/01/1930 |
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.
Datevalue Function Common Error
If you get an error from the Excel Datevalue function, this is likely to be the #VALUE! error:
Common Error:
| #VALUE! | - | Occurs if the supplied date_text cannot be recognised as a valid Excel date. |