Methods to convert text to date in Excel

August 1, 2022
208 Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

In many cases, to analyze business statistics, you need to manipulate dates in Excel to reply questions which includes How much money did you make these days or How do you examine to the identical day remaining week? And that can be hard when Excel doesn’t recognize the values as dates. Unfortunately, this is not unusual, especially when multiple users are typing this information, copying and pasting it from other systems, and importing it from databases. Consuming

Find and Replace Method

Probably one of the most common mistakes beginners makes when typing dates into Excel is doing so with the full stop character to separate the day, month, and year. Excel will not understand it as a date and could store it as textual content. However, you could fix this trouble with the Find and Replace tool. By replacing the full stops with slashes (/), Excel will automatically identify the values as dates.

1. Select the columns on which you want to perform the find and replace.

2. Click Home > Find & Select > Replace—or press CTRL + HIn the Find and Replace window, type a full stop (.) in the “Find what” field and a slash (/) in the “Replace with” field. Then, click “Replace All.”

All full stops are converted to slashes and Excel recognizes the new format as a date.

Using Substitute Formula

=VALUE(SUBSTITUTE(A2,.,/))

If your spreadsheet records changes regularly and also you need an automated answer for this, you can use the SUBSTITUTE formula.

The SUBSTITUTE function is a text function, so cannot convert it to a date on its own. The VALUE formula will convert the text fee to a numeric value.You can do this using the “Number Format” list on the “Home” tab.

Converting the Date Format

If you receive the data in the following formats, following steps are required.

This format is fairly standard in technology as it eliminates confusion about how different countries store dates. However, Excel doesn't understand it at first.

1. For a quick manual solution, you could use Text to Columns.

2. Select the range of values you need to convert and then click Data > Text to ColumnsThe Text to Columns wizard appears. Click “Next” on the first  and second steps so you are at step 3, as shown below.

3. Choose Date and then select the date format being used in the cells from the list. In this example, we are dealing with a DYM format.

Converting text to date by Formula

If you want a system solution, you may use the Date characteristic to create a date. It is utilized along side with the left, center and right textual content functions to extract 3 parts of a date (day, month and 12 months) from the contents of a cell.

The method below suggests this formulation using sample data.

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

Using either of those strategies, you can convert any eight-digit wide variety value. For instance, you might receive the date in a ddmmyyyy format or a mmddyyyy format.


Did you learn how to convert text to Date format 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.