# 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**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.