How to Use DATEVALUE Function in Excel- 4 Examples

DATEVALUE is an Excel function that converts a text string representing a date into a serial number that Excel can recognize as a date. When working with data that includes dates represented as text, the DATEVALE function in Excel can be extremely useful.

Syntax of DATEVALUE Function in Excel

=DATEVALUE(date_in_text_format)

Argument of DATEVALUE Function

date_in_text_format: Valid date in text format, should be any date between January 1, 1900, and December 31, 9999

Return of DATEVALUE Function

Returns the serial number of the date given in the argument from January 1, 1900

Example #1: Simple application of DATEVALUE Function

Suppose you want to evaluate the date values of the given random dates below.

Enter the following formula in the output cell C3

=DATEVALUE(B3)

Press Enter and AutoFill the residual cells in column C

Converting Number to Date in Excel (dd, mm, yyyy):

Now you want to convert the number returned by the DATEVALUE Function into the formatted date.

• Enter the following formula in the output cell D3

=C3

• After pressing the Enter Key, AutoFill the remaining cells in column D

• Select the cell range D3:D7

• From the “Home tab”, go to the ‘’Number section’’
• Click on the ‘’Number format’’ dropdown list and select “Short date”

The result is 

Example #2: Reverse DATEVALUE Function in Excel

We reversed the DATEVALUE function to the date format in the previous example. Now we want to convert the numbers into date format according to user preference formatting (m/d/yy, d/m/yy, or dd/mm/yyyy)

As the TEXT function is designed to provide a specific format, so we will use the TEXT function to achieve the desired format.

Apply the following formula to get your desired output

=TEXT(C3,"D/M/YY")      //returns the date in D/M/YY format

=TEXT(C3,"M/D/YY")      //returns the date in M/D/YY format

=TEXT(C3,"DD/MM/YYYY")        //returns the date in DD/MM/YYYY format

Example #3: Finding Dates from the Left, Right, and Middle of the TEXT Using DATEVALUE Function

Finding Date Value from the Left side of Text

Suppose you want to find the equivalent date value for the date given below:

Using the formula below, you can extract the date value from a long string where the dates are at the beginning of the string:

=DATEVALUE(LEFT(B2,9))

Explanation:

The LEFT function returns nine characters from the beginning of the string. And the dates are formatted as having nine characters in length.

The DATEVALUE function then returns the number corresponding to the date returned by the LEFT function.

Finding Date Value from the Right side of Text

Similarly, you can find the date value from the long string if the date is situated at the right side of the string, using the formula given below:

=DATEVALUE(RIGHT(B2,9))

Finding Date Value from the Middle of Text

Now what if the date stays in the middle position of the string?

Using the formula given below, you can solve the above problem easily.

=DATEVALUE(MID(B2,FIND(" ",B2)+1,10))

Explanation: 

• FIND(” “,B2) starting from the right to left, finds the space ” ” 
• FIND(” “,B2)+1 adds one to the position of the found space ” “
• MID(B2,FIND(” “,B2)+1,10) gets the 10 characters from the position of the space ” ” + 1 and pushes it inside of the DATEVALUE function.
• Then the date value function returns the numerical number of the dates returned by the MID function.

Let’s see the formula evaluation for row 2:

=DATEVALUE(MID(B2,FIND(" ",B2)+1,10))

=DATEVALUE(MID(“Morning 2/12/2023 10.00AM”,FIND(" ",B2)+1,10))

=DATEVALUE(MID(“Morning 2/12/2023 10.00AM”,FIND(" ",“Morning 2/12/2023 10.00AM”)+1,10))

=DATEVALUE(MID(“Morning 2/12/2023 10.00AM”,8+1,10))

=DATEVALUE(MID(“Morning 2/12/2023 10.00AM”,9,10))

=DATEVALUE(“2/12/2023”)

=44969

Example #4: Finding Date Values from separated cells containing Date, Month, and Year

Suppose you are given the day, month, and year in the separate cells. You want to get date values from the data given below:

To get the corresponding date values use the formula given below

=DATEVALUE(B2&"/"&C2&"/"&D2)

Then you can follow the ”Convert number to date ” portion from Example #1 to convert date values to formatted dates.

Things to Keep in Mind about DATEVALUE Function in Excel

• It transforms any specified date into a serial number beginning with 1900. It only recognizes dates between January 1, 1900, and December 31, 9999.
• #VALUE! Error – This tends to happen when the date provided in the argument is either not inside of January 1, 1900, and December 31, 9999, or the date is not formatted as text.

Conclusion:

Finally, the DATEVALUE function in Excel is a powerful tool for converting text strings representing dates to serial numbers representing dates. This function is especially useful when working with large datasets containing dates in various formats or when performing date-based calculations.

 If you have any queries on the DATEVALUE function in Excel, please comment below.