DATEVALUE function

Excel DATEVALUE Function allows you to convert a date represented as text into a valid date in Excel. It’s a handy tool for working with dates when your data is stored as text.




date_textThe text representation of a date you want to convert into a date value. It can be a cell reference, a date entered in quotes (e.g., “01/31/2023”), or a formula that results in a text date.

How to Use

To use the DATEVALUE function, follow these steps:

1. Start by selecting the cell where you want the converted date to appear.

2. In the cell, type or reference the date in text format that you want to convert. For example, you can enter it directly in quotes like “01/31/2023” or reference a cell where the date is stored as text.

3. Next, enter the DATEVALUE function, passing the cell reference or the text date as the argument. For example, if the text date is in cell A1, you can use the formula:

4. Press Enter, and Excel will convert the text date into a valid date value.

5. The result will be displayed in the selected cell as a date value, allowing you to perform various date-related calculations and formatting on it.


Here are a few examples to illustrate how to use the DATEVALUE function:

Example 1: Converting a text date to a date value

If cell A1 contains the text date “01/31/2023,” you can use the formula:

This will return the date as a valid Excel date value.

Example 2: Using a direct text entry

You can also use the DATEVALUE function with a date entered directly in quotes:

This will convert the text date “05/15/2023” into an Excel date value.

Example 3: Dealing with optional arguments

The DATEVALUE function only has one required argument, which is the text date. If you want to handle optional arguments, you can use them like this:

Optional arguments are not necessary for basic date conversion, so you can simply provide the required date_text.

Additional Information

If you encounter date conversion issues, ensure that the text date you’re trying to convert is in a recognizable format. Excel may not be able to convert text that does not follow common date formats. Additionally, check your regional settings, as date formats can vary between regions.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.