Dates play an important role when we need to analyze trends. This is one of the most important things which you need to capture in the right way. Apart from the basic date format which is used by Excel to store dates. There are some other data formats that are acceptable.
Now the thing is: When we capture a date in the wrong format Excel treats that date as a text and, then we can’t use that date further in any calculation.
When it comes to textual data formats there is no one particular thing. It depends on the person who is entering those dates. The bottom line is: We do need some methods which we can use to convert a text to an actual date with the right format.
So today, in this post, I’d like to share with you 10 methods that we can use to convert a text to date format in Excel. For converting a text to date we need to use a combination of different functions.
So let’s get started.
1. Convert a Text to a Date
Using the DATEVALUE function is a basic method to convert a date into an actual date which is stored as text.
Let’s say you have a list of dates in your worksheet and all the cells where dates are entered have text format but all those dates are in the correct date format.
In the below example, we have a date in a cell but when I try to add a number to that date I’m getting an error, even though the format of the data is correct.
But if you look at the cell format, it’s text. So here, is the best way to use the DATEVALUE function. You just need to refer to that cell in the function and it will convert that data into actual date.
And, then you can change its format to date.
Note: There are some situations where DATEVALUE can’t help us to get a date from a text. There is n number of irregular formats of dates and to deal with all those we need to create a custom formula using different functions.
In the next 10 examples, I will share with you how can we use these different functions.
2. Date with Back Slashes
The Excel date system supports dates with forwarding slashes. And, if we have a date where we have backslashes, it will treat it as a text.
To solve this problem we can use the below formula.
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
3. Date with Month Name
Now, we have a date format where the month is entered with its name. For Excel, this is not a date anymore, even if it’s presenting a date clearly.
The below formula can help us with this.
=DATEVALUE(RIGHT(A3,2)&"-"&TEXT(MID(A3,6,3),"MMM")&"-"&LEFT(A3,4))
4. Date with Dots
It’s a common kind you the problem where we get dots inside a date. This is a kind of format used by people who are not aware that this is not a proper date format.
And, this is the formula to correct it.
=DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2))
5. Date with the Month Name and a Comma
This format is almost the same as the format we discussed in point 3.
And, we can correct it with the below formula.
=DATEVALUE(LEFT(A5,2)&"-"&TEXT(MID(A5,4,3),"MMM")&"-"&RIGHT(A5,4))
6. Date with the Day Name
Sometimes people store a date with the day’s name. And, if that date is not in a proper format Excel will treat it as text.
Use this formula for this type of text date.
=DATEVALUE(MID(A6,FIND(",",A6)+6,2)&"-"&MID(A6,FIND(",",A6)+2,3)&"-"&RIGHT(A6,4))
7. Date with a Day Name in the End
Here we have a date with the day name at the end. Now, the problem with this format is it has a comma between the date and day name.
But, we can get the valid date using the below formula.
=DATEVALUE(MID(A7,10,2)&"-"&MID(A7,6,3)&"-"&LEFT(A7,4))
8. Date Having a Suffix with Day
We have a “th” suffix with the day number and it makes it a text instead of a date as Excel is not able to recognize it.
The below formula will help you to get the real date.
=DATEVALUE(LEFT(A8,FIND("th",A8)-1)&"-"&MID(A8,FIND(" ",A8)+1,3)&"-"&RIGHT(A8,4))
9. Date with Space Between Day, Month, and Year
When we have a date format with space between day, month, and year, we can use the below formula to correct the date format.
=DATE(RIGHT(A9,4),MID(A9,4,2),LEFT(A9,2))
10. Date with Short Month Name
With a date format where we have a short month name at the start of the date.
We can use the following formula to convert it to a valid date format.
=DATEVALUE(MID(A10,5,2)&"-"&LEFT(A10,3)&"-"&RIGHT(A10,4))
11. Date Without Any Space Between Day, Month, and Year
And for a date where there is no space between day, month, and year.
The below formula will help us to get the correct date format.
=DATE(RIGHT(A11,2),MID(A11,3,2),LEFT(A11,2))