It is the text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format. Advanced Excel functions you must know. Analysis of business data often requires working with date values in Excel to answer questions such as “how much money did we make today” or “how does this compare to the same day last week?” And that can be hard when Excel doesn’t recognize the values as dates.Unfortunately, this is not unusual, especially when multiple users are typing this information, copying and pasting from other systems and importing from databases.In this article, we will describe four different scenarios and the solutions to convert the text to date values.Probably one of the most common mistakes beginners make when typing dates into Excel is doing so with the full stop character to separate the day, month, and year.Excel will not recognize this as a date value and will go ahead and store it as text. Click “Next” on the first two steps so that you are at step three, as shown in the image below. Trying to form a formula that picks up data from other cells. Since we launched in 2006, our articles have been read more than 1 billion times. The results are shown below:As we can see, this function ignores the time information in text strings. The formula used is DATEVALUE(A1). They are DATEVALUE and VALUE.The DATEVALUE function will convert text into a date value (probably saw that coming), while the VALUE function will convert text into a generic number value. The result will still need to be formatted as a date.This formula will produce the same results except for in row 4, where the time value is also maintained.The results can then be formatted as date and time, or as a date to hide the time value (but not remove).Alan Murray has worked as an Excel trainer and consultant for twenty years. Select the dates whose format your want to change, or empty cells where you want to insert dates. For example, LINEST is one of the worksheet functions that will work properly only if you array-enter the formula that contains the function. If you would like a formula solution, then you could use the Date function to construct the date. Get Cell Value with Excel VBA. And that will be a demonstration of the functions’ minor differences.The DATEVALUE formula below would convert each one to a date value.Notice how the time was removed from the result in row 4. Unfortunately, they have been stored as text and need converting.For these scenarios, it is easy to convert using a variety of techniques.For this article, I wanted to mention two functions to handle these scenarios. In this example, we are dealing with a YMD format. a) Read cell: just convert it to string using format options. In Excel, there are some formulas can help you to quickly extract part of text. We shall use this function to convert the different text representations of dates given to us.The DATEVALUE function in Excel, when interpreting the supplied date_text argument, follows simple rules to interpret the year. Converts imported dates from text to proper date formatLearn 100% online from anywhere in the world. The results, as shown above, are returned as numbers due to the “General” formatting type. Note: INDIRECT is a volatile function and can cause performance problems in more complicated worksheets.. With INDEX. I recently saw a spreadsheet with the following function =LEFT(A2,8) where cell A2 was equal to 08/06/12 12:23 PM. If, in a column of dates, each date is in the order of year, month, and date, and you select Date along with a date type of MDY, Excel converts the column to General format.

Then, click “Replace All.”All full stops are converted to slashes and Excel recognizes the new format as a date.If your spreadsheet data is regularly changing, and you want an automated solution for this scenario, you could use The SUBSTITUTE function is a text function, so cannot convert it to a date on its own.