The Article of DATEVALUE explains how to use Excel functions to change text into dates and numbers into dates. It also shows how to convert text strings into dates without using formulas. You’ll also learn a quick way to change numbers into date format.
When you work with Excel, you might import dates from a .csv file or another external source. Sometimes these dates might appear as text entries instead of recognized date formats.
There are several methods to convert text to dates in Excel, and this tutorial covers them all. This way, you can choose the most suitable technique for converting text to dates based on your data format and whether you prefer using formulas or not.
How to difference normal excel date from "text Date"
When you bring data into Excel, sometimes dates might not format correctly. The imported entries might seem like regular Excel dates, but Excel doesn’t recognize them as such. Instead, it treats them as text. This means you can’t sort your table correctly by date, and you can’t use these “text dates” in formulas, PivotTables, charts, or other Excel tools that work with dates.
There are a few clues that can help you figure out if an entry is a date or just text:
Dates | Text values |
|
|
How to convert number to date in Excel
Since all Excel functions that convert text to dates give you a number as the result, let’s focus first on how to convert numbers into dates.
As you may already know, Excel stores dates and times as serial numbers. The formatting of a cell determines how Excel displays these numbers as dates. For instance, January 1, 1900, is stored as the number 1, January 2, 1900, as 2, and January 1, 2015, as 42,005.
When you use date functions in Excel to calculate dates, the result is usually a serial number that represents a specific date. For example, if you use the formula =TODAY()+7 and it shows a number like 44286 instead of displaying the date that’s 7 days after today, it doesn’t mean the formula is incorrect. It simply means the cell’s format might be set to General or Text instead of Date, which is why it shows the serial number rather than the date itself.
To convert such serial number to date, all you must do is change the cell number format. For this, simply pick Date in the Number Format box on the Home tab.
To apply a format other than default, then select the cells with serial numbers and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK.
Advantage of "DATEVALUE Function"
The “DATEVALUE” function is quite useful, especially when dealing with databases imported from other sources like CSV or text files. These imports often turn dates into text format.
DATEVALUE comes in handy here because it helps convert these imported text dates into proper date formats easily. Manually changing each date one by one to the correct format can be very challenging and time-consuming.
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 a very handy function that can be used in various situations:
– It’s useful for converting dates from text format to proper date format in imported databases.
– It’s helpful when transforming existing databases, especially when you need to summarize data at the date level.
– Or in any other database where there’s a need to change the date format, the “DATEVALUE” function can be applied effectively.
Things to Remember while using DATEVALUE
– The “DATEVALUE” function returns a value in the correct date format. If the function doesn’t find a valid date in text format, it will show an error like #VALUE!.
– The output of the DATEVALUE function might appear as a number format, for example, March 18, 2020, displayed as 43908. In such cases, you should change the cell format to Date Format to see the date properly.
Syntax & Description
=DATEVALUE(date_text)
date_text, argument is used to give the cell reference. It is the cell number that is to be converted to proper Date Format
Change the Text Date to Proper Date Format
Suppose we have a database where dates appear in different text formats, and we need to convert them to the proper date format. Here’s how we can proceed:
We can achieve converting dates from various text formats in the database into the correct date format quite easily.
Change Output of DATAVALUE function from Number to Proper Date Format
As mentioned earlier, the output of the DATEVALUE function might appear in number format, such as displaying March 18, 2020 as 43908. In such cases, you should change the cell format to Date Format. Here are the steps to convert the format:
Hope you liked. Happy Learning.
Don’t forget to leave your valuable comments!
