Business data analysis often requires working with date values in Excel to answer questions like «how much money did we make today» or «how does that compare to the same day last week?». And it can be tricky if Excel doesn’t recognize the values as dates.
Unfortunately, this is not unusual, especially when multiple users type this information, copy and paste from other systems, and import from databases.
In this article, we describe four different scenarios and solutions for converting text to date values.
Dates containing full stop / period
Probably one of the most common mistakes newbies make when entering dates in Excel is using a full stop character to separate the day, month, and year.
Excel won’t recognize this as a date value and will save it as text. However, this problem can be solved using the Find and Replace tool. By replacing the dots with forward slashes (/), Excel will automatically identify the values as dates.
Select the columns you want to search and replace on.
Click Home > Find & Select > Replace or press Ctrl+H.
In the Find and Replace window, type a period (.) in the Find What box and a forward slash (/) in the Replace With box. Then click Replace All.
All breakpoints are converted to slashes and Excel recognizes the new format as a date.
If your spreadsheet data changes regularly and you need an automatic solution for this scenario, you can use the REPLACE function.
= ЗНАЧЕНИЕ (ПОДСТАВИТЬ (А2, "", "/"))
The SUBSTITUTE function is a text function, so it cannot convert it to a date on its own. The VALUE function converts a text value to a numeric value.
The results are shown below. The value must be formatted as a date.
You can do this using the Number Format list on the Home tab.
The example here of the full stop separator is typical. But you can use the same technique to replace or replace any delimiter character.
yyymmdd format conversion
If you are receiving dates in the format shown below, this will require a different approach.
This format is fairly standard in technology as it removes any ambiguity about how different countries store their date values. However, Excel will not natively understand this.
For a quick manual solution, you can use text to columns.
Select the range of values you want to convert and click Data > Text to Columns.
The Text to Columns wizard opens. Click «Next» on the first two steps to go to the third step as shown in the picture below. Select «Date» and then select the date format used in the cells from the list. In this example, we are dealing with the YMD format.
If you need a formula based solution, you can use the Date function to construct the date.
This will be used along with the text functions Left, Mid and Right to extract the three parts of a date (day, month, year) from the contents of the cell.
The formula below shows this formula using our sample data.
= ДАТА (ЛЕВЫЙ (A2,4), MID (A2,5,2), вправо (A2,2))
Using any of these methods, you can convert any eight-digit numeric value. For example, you can get the date in the format ddmmyyyy or in the format mmddyyyy.
DATEVALUE and VALUE functions
Sometimes the problem isn’t with the delimiter character but with an awkward date structure simply because it’s stored as text.
Below is a list of dates in various structures, but they are all recognizable to us as a date. Unfortunately they were saved as text and need to be converted.
For these scenarios, it is easy to convert using various methods.
In this article, I wanted to mention two features for handling these scenarios. They are DATEVALUE and VALUE.
The DATEVALUE function converts text to a date value (probably anticipates this), and the VALUE function converts text to a total number value. The differences between them are minimal.
In the image above, one of the values also contains time information. And this will be a demonstration of minor differences in functions.
The DATEVALUE formula below will convert each to a date value.
= ДАТАЗНАЧ (А2)
Notice how the time has been removed from the result in line 4. This formula strictly returns the date value only. The result will still need to be formatted as a date.
The following formula uses the VALUE function.
= ЗНАЧЕНИЕ (А2)
This formula will give the same results, except for row 4, where the time value is also stored.
The results can then be formatted as a date and time, or as a date to hide the time value (but not remove it).
