The Excel DATEVALUE function converts a date represented as a text string into a valid excel date. For example, the formula =DATEVALUE(“3/10/1975”) returns a serial number (27463) in the Excel date system that represents March 10, 1975. Proper Excel dates are more useful than text dates since they can be directly manipulated with formulas and pivot tables.
Convert the date in text format to a valid date
A valid Excel time as a serial number
- date_text – A valid date in text format.
Sometimes, dates in Excel appear as text values that are not recognized as proper dates. The DATEVALUE function is meant to convert a date represented as a text string into a valid excel date. Proper Excel dates are more useful than text dates since they can be formatted as a date, and directly manipulated with other formulas.
The DATEVALUE function takes just one argument, called date_text. If date_text is a cell address, the value of the cell must be text. If date_text is entered directly into the formula it must be enclosed in quotes.
To illustrate how the DATEVALUE function works, the formula below shows how the text “3/10/1975” is converted to the date serial number 27463 by DATEVALUE]
=datevalue("3/10/1975") // returns 27463
Note that DATEVALUE returns a serial number, 27463, which represents March 10, 1975, in Excel’s date system. A date number format must be applied to display this number as a date.
In the example shown, column B contains dates entered as text values, except for B15, which contains a valid date. The formula in C5, copied down, is:
Column C shows the number returned by DATEVALUE, and column D shows the same number formatted as a date . Notice that Excel makes certain assumptions about missing day and year values. Missing days become the number 1, and the current year is used if there is no year value available.