How to Use DATEVALUE Function in Excel (6 Suitable Examples)

DATEVALUE function in Microsoft Excel is generally used to convert a text date into the date-time number codes. In this article, you’ll get to learn how you can use this DATEVALUE function properly and effectively in different cases in your Excel spreadsheet.

The above screenshot is an overview of the article which represents an application of DATEVALUE function in Excel. You’ll learn more about the dataset as well as the methods and functions to extract dates and then change, customize, or fix date formats in the following sections of this article.

Introduction to DATEVALUE Function

• Function Objective:

Converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code.

• Syntax:

=DATEVALUE(date_text)

• Argument Explanation:

• Return Parameter:

The function will return with the date-time code that has to be formatted again to convert into a date value.

How to Use DATEVALUE Function in Excel: 6 Suitable Examples

1. Using DATEVALUE Function to Convert Text Date into Number Format

In Column B, a number of dates are present but all of them are in text format. We have to use DATEVALUE function to convert these text dates into date-time codes and then we’ll customize the number format.

📌 Step 1:

➤ Select the output Cell C5 and type:

➤ Press Enter, and autofill the entire column by using Fill Handle.

You’ll find the numbers that represent the date-time codes.

📌 Step 2:

➤ Now select all the numbers in Column C.

➤ Under the Home ribbon, click on the Format Cell dialogue box icon.

➤ From the Date category, select a suitable date format you prefer.

➤ Press OK and you’re done.

You’ll see all the dates in the correct and selected format in Column C.

2. Combining Day, Month and Year Numbers with DATEVALUE Function from an Imported Data

When we have to import date data from another source, sometimes days, months, and year numbers return as split texts like in the picture below. So, in this case, we’ll have to combine the data from Columns B, C, and D to construct a proper date format in Column E. We’ll use Ampersand(&) to concatenate these data and add slashes(/) as separators in the date values.

📌 Step 1:

➤ Select Cell E5 and type:

➤ Press Enter, and autofill the entire column with the Fill Handle.

📌 Step 2:

➤ Now like the previous method, change the date-time code numbers into proper date format by selecting the suitable date format from the Number group of commands.

3. DATEVALUE with TIMEVALUE to Show Both Dates & Times

Now in Column B, you’re seeing the dates with times but all of them are in text format. If we use only the DATEVALUE function, it’ll extract the dates only and skip times. So, we have to combine the TIMEVALUE with the DATEVALUE function to show both dates and times. The TIMEVALUE function is almost similar to the DATEVALUE function but the TIMEVALUE function extracts time only from a text time.

📌 Step 1:

➤ In Cell C5, we have to type:

➤ Press Enter and fill down the rest of the cells with the Fill Handle option.

📌 Step 2:

➤ Now open the Format Cells dialogue box again from the Number group of commands, and select a suitable format from the Date category that shows both date and time.

➤ Press OK and you’re done.

Like the screenshot below, you’ll be shown dates as well as times in proper format in Column C.

4. Extracting Date from the Beginning of a Text String with DATEVALUE and LEFT Functions

If we have a date text at the beginning of the cell and the cell contains some other data, then the DATEVALUE function will not be able to extract the date-time code and as a result, you’ll be shown a #VALUE! error message. So, what we have to do is use the LEFT function to extract the data from the beginning or left of a text string. Then DATEVALUE function will convert this extracted data into date-time code.

📌 Step 1:

➤ In Cell C5, the related formula with DATEVALUE and LEFT will be:

➤ Press Enter, and autofill the entire column with the Fill Handle. You’ll be shown the date-time codes as return values.

In the arguments of the LEFT function, 2nd argument represents the number of characters in a text string from the left or the beginning. As our date format is with 9 characters, we’ve defined the 2nd argument of the LEFT function with 9.

📌 Step 2:

➤ Now convert the number format to the date format for Column C and you’ll find the restaurant values in proper date format.

5. Pulling Out Date from the Middle of a Text String with DATEVALUE, MID, and FIND Functions

If the date is present in the middle of a text string, let’s say the date is right after the 1st space, then we can apply this method by using DATEVALUE, MID, and FIND functions to pull out a date efficiently.

📌 Step 1:

➤ In Cell C5, we have to type:

➤ After pressing Enter and auto-filling the entire column with the Fill Handle, we’ll find the date-time codes.

So, how does this formula work, right? Well, the FIND function searches for the space and returns with the position of the 1st space character in the text string. Now, the MID function extracts the number of characters based on the position of the starting character that has been found through the FIND function already. As the 3rd argument in the MID function represents the length of the characters, we have to define the total number of characters for the date value.

📌 Step 2:

➤ Now modify the number format for Column C and convert them into date values. You’ll be able to find the expected results in the exact date format.

6. Bringing Out Date from the Right of a Text String with DATEVALUE and RIGHT Functions

By using the DATEVALUE and RIGHT functions together, we can extract a date value from the absolute right or the end of a text string. The RIGHT function works like the LEFT function but the difference is, this RIGHT function shows the characters of a text string from the right whereas the LEFT function does it from the left or beginning of a text string.

📌 Step 1:

➤ In Cell C5, the related formula with DATEVALUE and RIGHT functions will be:

➤ Press Enter and autofill the rest of the cells in Column C with the Fill Handle.

📌 Step 2:

➤ Convert the date-time codes into date format now and you’ll find the desired results at once.

💡 Things to Keep in Mind

🔺 DATEVALUE function returns with only the date. If a time is present with a date in text format, the DATEVALUE function will ignore the time value and will extract only the date value.

🔺 The date code starts with 1 from the date of 01/01/1900 and sequentially it increases with each next date which means each specific date has a date code. DATEVALUE function shows that date code 1st while extracting the date from a text format.

🔺 You’ll be shown #VALUE! Error if the DATEVALUE function cannot recognize the date from a text format.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all of the methods mentioned above to use the DATEVALUE function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

<< Go Back to Excel Functions | Learn Excel