Excel DATEVALUE Function

Convert Text Values to Dates with Excel's DATEVALUE Function

Converting Text Data into Dates with DATEVALUE. © Ted French

DATEVALUE and Serial Date Overview

The DATEVALUE function can be used to convert a date that has been stored as text into a value that Excel recognizes. This might be done if data in a worksheet is to be filtered  or sorted by date values or the dates are to be used calculations - such as when using the NETWORKDAYS or WORKDAY functions.

In PC computers, Excel stores date values as serial dates or numbers. Starting with January 1, 1900, which is serial number 1, the number continues to increase every second. On January 1, 2014 the number was 41,640.

For Macintosh computers, the serial date system in Excel begins at January 1, 1904 rather than January 1, 1900.

Normally, Excel automatically formats date values in cells to make them easy to read - such as 01/01/2014 or January 1, 2014 - but behind the formatting, sits the serial number or serial date.

Dates Stored as Text

If, however, a date is stored in a cell that has been formatted as text, or data is imported from an external source - such as a CSV file, which is a text file format - Excel might not recognize the value as a date and, therefore, will not use it in sorts or in calculations.

The most obvious clue that something is amiss with the data is if it is left aligned in the cell. By default, text data is left aligned in a cell while date values, like all numbers in Excel, are right aligned by default.

DATEVALUE Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the DATEVALUE function is:

= DATEVALUE(Date_text)

The argument for the function is:

Date_text - (required) this argument can be text data displayed in date format and enclosed in quotes - such as "1/01/2014" or "01/Jan/2014"
- the argument can also be the cell reference to the location of the text data in the worksheet.
- if the date elements are located in separate cells, multiple cell references can be concatenated using the ampersand ( & ) character in the order day/month/year, such as = DATEVALUE(A6 & B6 & C6)
- if the data contains just the day and month - such as 01/Jan - the function will add the current year, such as 01/01/2014
- if a two digit year is used - such as 01/Jan/14 - Excel interprets the numbers as:

  • 0 to 29 are interpreted as the years 2000 to 2029
  • 30 to 99 are interpreted as the years 1930 to 1999

#VALUE! Error Values

There are situations where the function will display the #VALUE! error value as shown in the image above.

  • The  #VALUE! error value is returned if the value of the Date_text argument is not between the dates January 1, 1900 and December 31, 9999 - example #2.
  • The  #VALUE! error value is returned if a cell reference used in the Date_text argument points to a cell containing a value formatted as a number or as a date - example #3.

Example: Convert Text to Dates with DATEVALUE

The following steps reproduce the example seen in cells C1 and D1 in the image above in which the Date_text argument is entered as a cell reference.

Entering the Tutorial Data

  1. Enter '1/1/2014 - note the value is preceded by an apostrophe ( ' ) to ensure the data is entered as text - as a result, the data should align to the left side of the cell

Entering the DATEVALUE Function

  1. Click on cell D1 - the location where the function results will be displayed
  2. Click on the Formulas tab of the ribbon
  3. Choose Date & Time from the ribbon to open the function drop down list
  4. Click on DATEVALUE in the list to bring up the function's dialog box
  5. Click on cell C1 to enter that cell reference as the Date_text argument
  6. Click OK to complete the function and return to the worksheet
  7. The number 41640 appear in cell D1 - which is the serial number for the date 01/01/2014
  8. When you click on cell D1 the complete function = DATEVALUE(C1) appears in the formula bar above the worksheet.

Formatting the Returned Value as a Date

  1. Click on cell D1 to make it the active cell
  2. Click on the Home tab of the ribbon
  3. Click on the down arrow next to the Number Format box to open the drop down menu of format options - the default format General is usually displayed in the box
  4. Find and click on the Short Date option
  5. Cell D1 should now display the date 01/01/2014 or possible just 1/1/2014
  6. Widening column D will show the date to be right aligned in the cell