Excel DATEVALUE Function
Note that Excel dates are stored internally as numerical values. Therefore, Excel generally doesn't recognise a text representation of a date as an actual date.
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 :
DATEVALUE( date_text )
where the date_text argument is a text string representing a date.
Interpretation of Date Text Strings
When reading in the supplied date_text argument, the Datevalue function follows simple rules that are the same as if you type a date directly into a cell. These are :
- If just a date and month are provided, the date returned 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
The following spreadsheet shows examples of the Excel Datevalue function:
|1||=DATEVALUE( "01/01/2015" )|
|2||=DATEVALUE( "01/01/15" )|
|3||=DATEVALUE( "01/01" )|
|4||=DATEVALUE( "01/01/29" )|
|5||=DATEVALUE( "01/01/30" )|
|1||42005|| - represents the date 01/01/2015|
|2||42005|| - represents the date 01/01/2015|
|3||42005|| - represents the date 01/01/2015|
|4||47119|| - represents the date 01/01/2029|
|5||10959|| - represents the date 01/01/1930|
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 2015, the returned date has the year 2015.
- 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. To do this:
Results with date formatting:
- Highlight the cell(s) to be formatted
- Right click with the mouse
- Select the Format Cells ... option and ensure the Number tab is selected
- Under the Category heading, select the option Date. Select a date format from the list on the right and click OK
The resulting formatted spreadsheet is shown on the rightabove.
For more details on formatting in Excel, go to the Excel Formatting page.
Further information and examples of the Excel Datevalue function can be found on the Microsoft Office website.
Datevalue Function Common Error
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.|