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 .
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_textis 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:
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.
Notice that the DATEVALUE formula in C15 fails with a #VALUE! error, because cell B15 already contains a valid date. This is a limitation of the DATEVALUE function. If you have a mix of valid and invalid dates, you can try the simple formula below as an alternative:
The math operation of adding zero will cause Excel will try to coerce the value in A1 to a number. If Excel can parse the text into a proper date it will return a valid date serial number. If the date is already a valid Excel date (i.e. a serial number), adding zero will have no effect, and generate no error.
DATEVALUE will return a #VALUE error if date_textrefers does not contain a date formatted as text.
In this example, the goal is to return a number, 1-12, for any month name of the of the year. For example, given the string "January" we want to return 1, "February" should return 2, and so on. If we had a valid Excel...
The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here. Zodiac signs are used in...
To get the date, we extract the first 10 characters of the value with LEFT:
LEFT(B5,10) // returns "2015-03-01"
The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE, which...
The Excel TIMEVALUE function converts a time represented as text into a proper Excel time. For example, the formula =TIMEVALUE("9:00 AM") returns 0.375, the numeric representation of 9:00 AM in Excel's time system. Numeric time values are more...
The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric...
The Excel NUMBERVALUE function converts a number in text format to numeric value, using specified decimal and group separators. This function can be used to convert locale-specific values into locale-independent values.