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.

Related Functions:


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:

1 =DATEVALUE( "01/01/2016" )
2 =DATEVALUE( "01/01/16" )
3 =DATEVALUE( "01/01" )
4 =DATEVALUE( "01/01/29" )
5 =DATEVALUE( "01/01/30" )
  A B
1 42005 - represents the date 01/01/2016
2 42005 - represents the date 01/01/2016
3 42005 - represents the date 01/01/2016
4 47119 - represents the date 01/01/2029
5 10959 - represents the date 01/01/1930

In the above example spreadsheet:

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):

Excel Format Cells Drop Down Menu on Ribbon With Date Option Selected
 Results with date formatting:
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.