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.

datevalue function overview in excel

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.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare 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:
Argument Compulsory/Optional Explanation
date_text Compulsory Date in text format.
  • Return Parameter:

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


6 Suitable Examples of How to Use DATEVALUE Function in Excel

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.

use of datevalue function in excel

📌 Step 1:

➤ Select the output Cell C5 and type:

=DATEVALUE(B5)

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

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

use of datevalue function in excel

📌 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.

use of datevalue function in excel

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

use of datevalue function in excel

Read More: How to Use DATE Function in Excel (8 Examples)


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 Column B, C, 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.

use of datevalue function in excel

📌 Step 1:

➤ Select Cell E5 and type:

=DATEVALUE(B5&"/"&C5&"/"&D5)

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

use of datevalue function in excel

📌 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.

use of datevalue function in excel

Read More: How to Use Excel MONTH Function (6 Examples)


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 DATEVALUE function, it’ll extract the dates only and skip times. So, we have to combine TIMEVALUE with DATEVALUE function to show both dates and times. TIMEVALUE function is almost similar to DATEVALUE function but TIMEVALUE function extracts time only from a text time.

use of datevalue with timevalue function in excel

📌 Step 1:

➤ In Cell C5, we have to type:

=DATEVALUE(B5)+TIMEVALUE(B5)

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

use of datevalue with timevalue function in excel

📌 Step 2:

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

➤ Press OK and you’re done.

use of datevalue with timevalue function in excel

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

use of datevalue with timevalue function in excel

Read More: How to Use TIME Function in Excel (8 Suitable Examples)


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 cell and the cell contains some other data, then DATEVALUE function will not be able to extract date-time code and as a result, you’ll be shown a #VALUE! error message. So, what we have to do is using 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.

extractng date with datevalue from the beginning

📌 Step 1:

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

=DATEVALUE(LEFT(B5,9))

➤ Press Enter, 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, so we’ve defined the 2nd argument of LEFT function with 9.

extractng date with datevalue from the beginning

📌 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.

extractng date with datevalue from the beginning

Read More: How to Remove Time from Date in Excel (6 Approaches)


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:

=DATEVALUE(MID(B5,FIND(" ",B5)+1,9))

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

extractng date with datevalue from the middle in excel

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.

extractng date with datevalue from the middle in excel

Read More: How to Use DAYS Function in Excel (7 Examples)


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

By using 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:

=DATEVALUE(RIGHT(B5,9))

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

extractng date with datevalue from the right or end in excel

📌 Step 2:

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

extractng date with datevalue from the right or end in excel

Read More: How to Use EDATE function in Excel (5 Simple Examples)


💡 Things to Keep in Mind

🔺 DATEVALUE function returns with only date. If a time is present with a date as 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 that means each specific date has a date code. DATEVALUE function shows that date code 1st while extracting date from a text format.

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


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.


You May Also Like to Explore

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo