In this lesson, I’ll show you how to convert text to dates in Excel in a formula and non-formula way.
When you import data to Excel, it may come in many different forms, with different delimiters. It’s also important to be aware of dates notation used in a particular country. For example, people in the USA use different date format than the rest of the world.
There are many ways to convert text to date. I’ll show you all of them so you can choose a conversion technique that is most suitable in a given situation.
Check whether a value is a DATE or TEXT
It’s impossible to know whether a particular value is a date or text by looking at a spreadsheet. You have to check them. We can do it by using the ISTEXT function.
This function returns TRUE if the value is text, and FALSE otherwise.
Take a look at the following example. In column A, there are dates, but some of them are formatted as text and the other ones as dates.
If you press Ctrl + ` (grave accent), you are going to notice how these values are stored.
You can also check whether the text is formatted as a date by clicking it and looking at Home >> Number.
You have to be careful here. If you place a single apostrophe (‘) before date (or any other value or formula), Excel will treat it as text, even when the number format in Home >> Number shows DATE.
This apostrophe will be only visible inside the formula bar and not in a cell. The ISTEXT function will return TRUE.
How to convert a number to date
There is no DATE type in Excel. Dates are numbers formatted to dates.
The following example has numbers in column A, and the same numbers formatted as dates in column B. Increasing the number by 1, increases the date by one day, starting from 1/1/1900.
If you want to know more about the way Excel stores dates read the following article.
Change the date format
I’m going to show you two ways you can change the date format.
Use the TEXT function
If you want to change the date format and then convert it to text, you can do it with the TEXT function.
This function converts a value to text in a specific format.
|
1 |
=TEXT(A2,"dd-mm-yyyy") |
Format Cells window
If you want to change the format of existing dates, without converting them to text, you can do it by accessing the Format Cells window.
Select the cells you want to change the format and press Ctrl + 1.
Under the Date category, you can choose a country and a format you want to use. Choose d-m-yyyy.
This is the result we get.
Change text to date
If you have a date in a form of text, you can’t change it to different date format. First, you have to convert it to a number and then to date.
The DATEVALUE function takes a single argument, which is a date in a form of text.
Then the function converts this text to a number. The number can be later converted to date with different date format.
|
1 |
=DATEVALUE(A2) |
If you want to change a date to a number, you can use the VALUE function instead.
Convert 8-digit number to date
When you receive a file with dates written as an 8-digit number, first you have to recognize the country date formats (YMD, DMY, MDY). If you have many examples, it’s easy to figure it out.
| YMD | DMY | MDY |
| 20160101 | 11012011 | 11112018 |
| 20090520 | 21102016 | 07032017 |
| 20191127 | 07012009 | 11212016 |
If you just copy and paste the table, you will get this result.
Cells B4 and C3 don’t have leading zeros. That’s because Excel removes them by default. Even if you copy cells as text, the zeros will be removed. You can format cells as text and add zeros by hand, but there is a better way to do this.
Before copying cells, select empty cells where the values will be copied, right-click and choose Format Cells.
You can also use the Ctrl + 1 keyboard shortcut.
In the Category window, select Text.
Click OK.
Copy the table and paste it into Excel worksheet with Match destination formatting option.
After you paste these values, don’t forget to convert the rest of them to the General format.
Now, the leading zeros are in place and we can create our formulas. We are going to convert text to date using the DATE function.
The MID function
In this formula, we are going to use the MID function to separate values into days, months and years.
| YMD | Result YMD |
| =DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2)) | 1/1/2016 |
| DMY | Result DMY |
| =DATE(MID(B2,5,4),MID(B2,3,2),MID(B2,1,2)) | 1/11/2011 |
| MDY | Result MDY |
| =DATE(MID(C2,5,4),MID(C2,1,2),MID(C2,3,2)) | 11/11/2018 |
The LEFT, MID and RIGHT functions
In the second method, we will use two additional functions: LEFT and RIGHT. This formula is more readable because these two functions take two parameters, instead of three.
| YMD | Result YMD |
| =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) | 1/1/2016 |
| DMY | Result DMY |
| =DATE(RIGHT(B2,4),MID(B2,3,2),LEFT(B2,2)) | 1/11/2011 |
| MDY | Result MDY |
| =DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,3,2)) | 11/11/2018 |
When we use Text to Columns Wizard, we usually specify a delimiter. But here, we don’t have any. There is another option you can use to separate values, called Fixed width. We can use this feature because we have a consistent width of 8 digits.
Click Next.
Create two lines separating the values.
Click Finish. We don’t have to go to step 3. Our data will be converted to the General type.
Our example will be separated to Day, Month and Year.
Convert 6-digit number to date
If you get a list of dates that consists of 6 numbers, you have to modify the formula. If you know that there is no year older than 2000, you can use the following formula for DMY.
|
1 |
=DATE(20&RIGHT(B2,2),MID(B2,3,2),LEFT(B2,2)) |
This will give you the following result.
1/11/2011
If you don’t place 20& before the RIGHT function, the year will become 1911, instead of 2011.
Let’s suppose that you don’t have years before 1980. In this formula, we are going to convert every year smaller than 1980 to 2000s and bigger than 80 to 1900s.
|
1 |
=DATE(IF(INT(RIGHT(A2,2))>=80,19&RIGHT(A2,2),20&RIGHT(A2,2)),MID(A2,3,2),LEFT(A2,2)) |
Formula explanation
The formula converts a year that consist of two digits to an integer. It’s important to convert this to a number if you want to do comparisons. Next, it checks whether the number is bigger or equal to 80. If it returns TRUE then it adds 19 at the beginning. If it FALSE it adds 20.
Change delimiters
Find and Replace
If your dates as formatted as text and you have a non-standard delimiter, you can change them easily to slashes.
Take a look at the following example.
With selected dates (A2:A11) use the Ctrl + H. This keyboard shortcut opens the Find and Replace window. Here, you can replace one character with another.
Click Replace All. Our table changed and now dates are formatted as dates.
What happened? Some values converted to dates and the other ones stayed text. Dates are already converted to the US format (MDY). That’s why 12.07.2003 changes to date (December) and 23.12.2011 won’t because you don’t have 23rd month.
We have to make some language and region modifications outside Excel.
Region and Language settings
In the search area start typing “Control Panel” until the icon appears.
Make sure that you have the view by a category selected.
Under Clock and Region, select Change date, time, or number formats. In the Region window, click Additional Settings. Next, choose the Date tab.
Now, days, months and years are separated by slash instead of a dash.
Modify the Short date format.
Click Apply on both windows.
Let’s replace the characters one more time. Now, the dates are formatted properly.
The SUBSTITUTE function
Let’s switch back to the standard US format in the Region settings M/d/Y.
The SUBSTITUTE function works in a similar way as Find and Replace – it replaces the existing text with a new text in a text string.
|
1 |
=SUBSTITUTE(A2,"-","/") |
