The Excel DATEVALUE Function

Home » Excel-Built-In-Functions » Excel-Datevalue-Function

Search this site:

Custom Search

Related Function :

TIMEVALUE Function

Excel Dates & Times

Note that Excel stores dates & times as numerical values. It is purely the formatting of these numbers that tells Excel to display a date or a time.

For more information, see the page on Excel Dates & Times

Basic Description

The Excel DATEVALUE function converts a text string into an Excel date.

- ie. the function converts a string representing a date, into the integer that represents the date in Excel's date-time code.

The format of the Datevalue function is :

DATEVALUE( date_text )

where the date_text argument is a text string representing a date.

Note that 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 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.

Examples

The following spreadsheet shows several examples of the Excel Datevalue function. The format of the function is shown in the spreadsheet on the left and the result is shown in the spreadsheet on the right.

Formulas
Examples of use of the Excel Datevalue Function

Results

Note that, in the above example spreadsheet, the text string in cell A5 contains just a day and month. In this case the returned date will use the current year. As these examples were input into Excel in the year 2010, the returned date has the year 2010.

Formatting

There is a good chance that your result from the Excel Datevalue function will initially be displayed as a serial number (eg. 39814). This is because the cell containing the formula is not formatted to show dates.

To change the formatting to a date format:

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

For more details, go to the Excel Formatting page.

Return to the Excel Built-In Functions page

Information Functions

Date & Time Functions

Lookup & Reference Functions

Math Functions

Statistical Functions