How to convert text to date in Excel (Easy Formula)

August 1, 2022
511 Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

Excel typically does a decent job of recognising cell data. It frequently formats text-based cells as text, numeric cells as numeric cells, date cells as date cells—you get the point. Even if you've copied the content from another source, this doesn't always work.

If dates aren't structured properly, it might be challenging to transform them into accurate date values. There are a couple methods you can convert text to date data in Excel if you run into this issue. Here is what to do.

Formula

=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2)

Summary

You can analyse text and put together a valid date with a formula based on the functions DATE, LEFT, MID, and RIGHT to convert text in an unfamiliar date format to a proper Excel date. The formula in C6 in the illustrative case is:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

The DATE function is used in this formula to combine the year, month, and day variables to generate the date January 15, 2004.

Explanation

Using the three variables year, month, and day, the DATE function generates a valid date:

=DATE(year,month,day)

We extract each of these parts from a text string in cell C6 using the LEFT, MID, and RIGHT functions, and then use the results to feed the DATE function:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

The LEFT function extracts the first 4 characters from the left to represent the year, the MID function extracts the first 5–6 characters to represent the month, and the RIGHT function extracts the last 2 characters to represent the day. Direct returns of each result are made to the DATE function. The outcome is a legitimate Excel date that may be formatted anyway you'd like.

This strategy can be altered as necessary. For instance, row 8's unrecognised date format is dd.mm.yy. The formula in C8 is: 

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

Extended text

Sometimes dates in a lengthier format, such as Feb 27 2022 05:13:08, may exist that Excel is unable to correctly identify. In this situation, you might be able to modify the string in a way that enables Excel's SUBSTITUTE function to accurately identify the date. The formula below substitutes a comma and space (, ) for the second instance of a space ( ).

=SUBSTITUTE(A2, ,, ,2)+0 // add comma after month

Excel will recognise the date after we add the comma after the month name, but it still needs a little kick. We add zero at the end because of this. Excel attempts to translate the text into a number as a result of the math operation. A valid Excel date will be produced if this is successful. To show the date accurately, you might need to use date number formatting.

Omitted formulae

Try one of the solutions listed below before using a formula to manually parse and create a date from text. The first method nudges Excel a little and forces it to attempt evaluating the text as an integer.This frequently works since Excel dates are in reality numbers. If the procedure is successful, you might need to use a date format.

To fix dates, add zero

You may occasionally come across dates in a text format that Excel ought to be able to read. By adding a zero to the value, you might be able to compel Excel to turn the text values into dates in this situation. Excel will attempt to convert text values to integers when you add zero. Dates are essentially numbers, therefore using this method to convert dates to a text format that Excel can really understand is a wonderful idea.

Try Paste Special to Convert Dates in Place by Adding Zero.

1.In an empty cell, type 0 and then click Copy to Clipboard.

2.Pick the questionable dates.

3.Special > Paste > Values Add

4.Use a date format (if needed)

5.Zero can also be added to the following formula:

A1 has an unknown date, hence the equation is

=A1+0

To adjust dates, add text to columns

Using the Text to Columns Feature is another technique to allow Excel to detect dates:

Choose the dates column, then select Data > Text to Columns > Fixed > Finish.

If Excel is able to detect the dates, it will correct them all at once.

Did you learn about 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 the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience