Convert Text To Date

Dates play an important role when we need to analyze trends. And, this is one of the most important things which we need to capture in a right way.

In Excel, the right format to insert a date is mm/dd/yyyy. That’s the basic date format which is used by Excel to store dates. Apart for this, there are some other date formats which are acceptable.

Now the thing is: when we capture a date in a wrong format Excel treats that date as a text. And, then we can’t use that date further in any calculation.

When is comes to textual date 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 a right format.

So today, in this post, I’d like to share with you 10 methods which 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. Use DATEVALUE Function To Convert a Text to Date

Using the DATEVALUE function is a basic method to convert a date into an actual date which is stored as a 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 trying to add a number into that date I’m getting an error, even that the format of the date is correct.

Excel datevalue function to convert text to date

But if you look at the cell format, it’s text.

So here, the best way to use DATEVALUE function. You just need to refer that cell in the function and it will convert that date into an actual date.

convert text to date list with datevalue function

And, then you can change it’s format to a date.

Important Note

There are some situations where DATEVALUE can’t help us to get a date from a text. There are n number of irregular formats of dates and to deal with all those we need to create a custom formula using different functions. In next 10 examples, I will share with you how can we use these different functions.

text to date list

2. Date with Back Slashes

Excel date system support a date with the forward slashes. And, if we have a date where we have backslashes, it will treat it as a text.

convert text to date with black slashes invalid

To solve this problem we can use below formula.

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

convert text to date with black slashes

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 it’s presenting a date clearly.

convert text to date with month name full

Below formula can help us in this.

=DATEVALUE(RIGHT(A3,2)&"-"&TEXT(MID(A3,6,3),"MMM")&"-"&LEFT(A3,4))

convert text to date with month name full formula

4. Date with Dots

It’s a common kind you problem where we get dots inside a date. This is a kind format used by people who are not aware that this not a proper date format.

And, this 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 same as the format we have discussed in point 3.

And, we can correct it with below formula.

=DATEVALUE(LEFT(A5,2)&"-"&TEXT(MID(A5,4,3),"MMM")&"-"&RIGHT(A5,4))

6. Date with the Day Name

Sometimes people stores a date with the day name. And, if that date is not in a proper format Excel will treat it as a text.

Use this formula for this type to 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 in the end. Now, the problem with this format is it has a comma between date and day name.

But, we can get the valid date using 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.

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 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 in the starting 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.

Below formula will help us to get correct date format.

=DATE(RIGHT(A11,2),MID(A11,3,2),LEFT(A11,2))

  • Pingback: Convert Text To Sentence Case In Excel Using Text Formulas()

  • Morten Archer

    Hi. Thanks for an excelent site!
    In this article you write: “In Excel, the right format to insert a date is mm/dd/yyyy. That’s the basic date format which is used by Excel to store dates. Apart for this, there are some other date formats which are acceptable.” and follow up with: “Date with Dots …. is a kind format used by people who are not aware that this not a proper date format.”
    Wow! You are shooting yourself in the foot!
    The mm/dd/yyyy format is not the “basic format” for dates in Excel. Excel stores all date as desimal numbers. The date 2017-Aug-11 is stored as 42958. (Desimals may be added, representing time of the day.)
    Excel can present dates in various formats. I don’t think Microsoft has a preference of the mm/dd/yyyy format compared to the other available formats.
    The ISO standard 8601 is, in my opinion, the only place where a “proper” date format is defined. The internationally agreed format in this standard is: YYYY-MM-DD (2017-08-11). So both 16.12.2015 and 08/16/2017 can be said to not be “proper formats”. 16.12.2015 may not be a proper format in US or UK, but in many other parts of the world it is indeed the correct (=nationally prefered) format.
    The mm/dd/yyyy format is used in USA and UK but the rest of the world use other formats. The most common ones are supported by Excel.
    When you have an international audience it is important to be aware that the majority of Excel users live outside your own country. Often things are done differently in other countries. US/UK don’t allways have the “correct” or “best” solution to everything.
    Apart from this, the article is excelent! 🙂

  • Paul

    Selecting problem dates (using the wrong symbol such as or . as a separator) and using Text to Columns (on the Data tab) will often sort out date problems.
    In the first step, choose delimited, in the next step choose a symbol that’s not being used in the date, in the third step choose Date and the order of day, month, year for your dates. Change the destination if you don’t want to overwrite the original entries and click Finish

  • Morten Archer

    I really like your comprehensive approach to issues. I dont think we`ll find many other exhausiv explaination like this.
    But you start with the statement that mm/dd/yyyy is the «correct» date format in Excel. And you continue to claim that dates with dots are «format used by people who are not aware that this not a proper date format».
    This is not correct.
    The correct or default date format depends on your location/national setting. In most European countries (and maybe more) dd.mm.yyyy IS the preferred date format from long before MS Office, and it is the default date format in Excel when you select a European contry as your location.
    Please stop calling us using dots in dates as ignorant. It only displays your own ignorance. Please stop calling the mm/dd/yyyy format the right format. It may be the right format for people living in US, GB and its former colonies, but not correct for the rest of the world, wich I believe is the majority.