Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business
Are you a data enthusiast? Join us for a ride on your data and automate your stuffs

EXCEL FUNCTION – DATEVALUE

Datevalue function

How to Change TEXT into DATE Format

Microsoft Excel “DATEVALUE” is the very useful where dates are available as TEXT and need to convert them to proper DATE format. DATEVALUE function helps to convert the dates to proper DATE format to make the database useful.

DATEVALUE function is very easy to apply since there is only one argument in the function. DATEVALUE function may returns the output in number format and you may need to change the format of the cells to “DATE”. We will discuss the steps of how to convert the Text Dates into Date format and change the cells format.

 

Advantage of “DATEVALUE Function”

“DATEVALUE” can be used in various databases and really helped where database is imported from the other sources. Generally, where database is imported from other sources i.e. csv, text etc. data is converted in TEXT.

DATEVALUE function really helps in the above case and imported Text Dates can be converted in proper Date format in very easy way. Converting Date manually (one by one) to change the Date format is very difficult and

“DATEVALUE function” helps to apply the function in large database at once and makes the work easy, saves time and increases efficiency.

 

Where “DATEVALUE” can be used:

“DATEVALUE” is very useful and can be used in many situations. Like it can be used as follows:

– Imported databases, where details are in Text format and Date is to be converted in proper Date Format.

– Where transformation of existing database is required, need to prepare summary at Date level

– Or any other database where there is requirement changing date format then “DATEVALUE function” can be used

 

Things to Remember:

“DATEVALUE” returns to value in correct date format. If function did not find the date in Text format then it will give an error i.e. #VALUE!.

-Output of the DATEVALUE function may appear in Number format i.e. 18th March 2020 as 43908, in this case cell format should be changed to Date Format.

 

Syntax:
=DATEVALUE(date_text)
Syntax Description:

date_text, argument is used to give the cell reference. It is the cell number that is to be converted to proper Date Format

Example 1: Change the Text Date to Proper Date Format,

Suppose we have one database where Date is appearing in various Text Format and we need to convert them to proper Date format. We will follow as below:

Datevalue function

We can evaluate the output that Dates in various text formats in the database can easily be converted in proper Date Format.

Example 2: Change Output of DATEVALUE function from Number to Proper Date Format.

As we discussed, Output of the DATEVALUE function may appear in Number format i.e. 18th March 2020 as 43908, in this case cell format should be changed to Date Format. Follow below steps to convert the format.

Step 1: Go to Original Data

Datevalue function

Step 2: Select the Data Range

Datevalue function

Step 3: Go to Menu- >Number Section-> Short Date

Datevalue function

This will change the Number Format to Proper Date Format.

Hope you liked. Happy Learning.

Don’t forget to leave your valuable comments!

Leave a Reply

Your email address will not be published. Required fields are marked *