The DATEVALUE function with example in Excel

The DATEVALUE function in Excel is a powerful tool for converting a date represented as a text string into a valid Excel date. Let’s dive into how it works and explore some examples:

1. Syntax:

   =DATEVALUE(date_text)
   

   • date_text: A valid date in text format.

2. Purpose: The DATEVALUE function is used to convert text dates into Excel’s date system. Proper Excel dates are more useful than text dates because they can be directly manipulated with formulas and pivot tables.

3. Example: Suppose you have the following data:

   • Cell A1 contains the text date “3/10/1975”.
   • To convert this to a valid Excel date, use the formula:

     =DATEVALUE(A1)
     

     This will return the serial number 27463, which represents March 10, 1975 in Excel’s date system.

   Note that you’ll need to apply a date number format to display this serial number as an actual date. In the example below, column B contains dates entered as text values, except for B15, which contains a valid date. The formula in C5 (copied down) is:

   =DATEVALUE(B5)
   

   Column C shows the number returned by DATEVALUE, and column D shows the same number formatted as a date.

    

   !Excel DATEVALUE Example

    

   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.

4. Alternative Formula: If you have a mix of valid and invalid dates, you can try the simple formula below as an alternative:

   =A1 + 0
   

   The math operation of adding zero will cause Excel to 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.

Remember to replace cell references (A1, B5, etc.) with your actual date cells.