In Excel, we mainly work with data. We organize and manipulate data as per our requirements. We find out the required information from our managed data. But, in this article, we will discuss how to convert text to date and time in Excel. Most of the time when we copy any data that contains date and time information changes to text format. Then it becomes difficult for Excel to detect which is data and time information. And we need to convert that text data into date and time format.

Use of Mathematical Operators to Convert Text to Date and Time

In this section, we will different mathematical operators to convert text to date and time. We will use plus, minus, multiplication, and division operators here.

1.   Go to Cell C5 and Refer to Cell B5 here. Now, put a Plus (+) sign and add 0 with this. So, the formula becomes:

=B5+0

2.   Now press Enter.

3.   Now, in Cell D5 write: =C5 and then press Enter.

So, get the date & time from a text by using mathematical operators. Other operators will be used on the rest of the cells.

4.   Now, apply Multiplication (*), Division (/), Exact (–), and Minus (-) operators respectively on the cells C6, C7, C8, and C9. And we get the below result.

5.   Now, drag the Fill Handle icon to Cell D9.

Date with Back Slashes

The Excel date system supports dates with forward 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 below formula.

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

 Date with Month Name

Now, we have a date format where the month is entered with its name. For Excel, this is not date anymore, even if it’s presenting a date clearly.

The below formula can help us in this.

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

Date with Dots

It’s a common kind your 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 the formula to correct it.

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

Date with the Month Name and a Comma

This format is almost the same as the format we have 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))

Date with the Day Name

Sometimes people stores 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 to text date.

=DATEVALUE(MID(A6,FIND(“,”,A6)+6,2)&”-“&MID(A6,FIND(“,”,A6)+2,3)&”-“&RIGHT(A6,4))

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 the below formula.

=DATEVALUE(MID(A7,10,2)&”-“&MID(A7,6,3)&”-“&LEFT(A7,4))

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))

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))

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))

Did you learn how to convert text to Date in Excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience