قالب وردپرس درنا توس
Home / Tips and Tricks / Converting text to dates in Microsoft Excel

Converting text to dates in Microsoft Excel

  excel logo

Analyzing business data often requires working with dates in Excel to ask questions like "How much money do we make today?" Or "How's that compared to the same day last week?" This can be difficult if Excel does not recognize the values ​​as a date.

Unfortunately, this is not unusual, especially when multiple users enter this information, copy and paste from other systems, and import from databases.

In This Article We describe four different scenarios and solutions for converting the text into dates.

Data containing a period

One of the most common mistakes that beginners make when entering dates in Excel is the dot character used to separate the day, month, and year.

Excel does not recognize this as a date value and saves it as text. However, you can solve this problem with the Find and Replace tool. If you replace the points with slashes (/), Excel automatically recognizes the values ​​as a date.

Select the columns for which you want to search and replace.

 Dotted Date Dates

Click Start> Find & Select> Replace – or press Ctrl + H.

 Find and Select Values ​​in a Column

In the Find and Replace window, enter a period (.) In the Find What box and a slash (/) in the Replace With box. Then click on "Replace all".

 Filling Find and Replace Values ​​

All points are converted to slashes, and Excel recognizes the new format as a date.

 Dates Converted to Real Dates

If your spreadsheet data changes regularly and you want an automated solution to this scenario, you can use the SUBSTITUTE function.

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

The SUBSTITUTE function is a text function and therefore can not be converted to a separate date. The VALUE function converts the text value to a numeric value.

The results are listed below. The value must be formatted as a date.

 SUBSTITUTE Formula for Converting Text to Dates

Use the Number Format list on the Home tab.

 Format number as date

The example of a point delimiter is typical here. However, you can replace or replace any delimiter in the same way.

Converting the format "YYYYYMMDDD"

If you receive data in the format shown below, a different approach is required.

 Dates in Format YYYYMDD

This format is a standard in technology because it eliminates ambiguity about how different countries store their date values. However, Excel will not understand this at first.

For a quick manual fix, you can convert text to columns.

Select the range of values ​​that you want to convert, then click Data> Text in Columns.


The "Text in Columns" wizard appears. In the first two steps, click Next to be in step 3, as shown in the following figure. Select Date and then the date format used in the cells from the list. This example is a YMD format.

 Text in columns to convert eight-digit numbers to dates

If you want a formula solution, you can use the Date function to construct the date.

This is used in addition to the Left, Mid, and Right text functions to extract the three parts of a date (day, month, year) from the cell contents.

The following formula shows this formula with our sample data.

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

 Using the DATE formula with 8-digit numbers [19659003] You can use any of these techniques to convert any eight-digit number. For example, you may receive the date in the format DDMMMMYYY or MMMMMYYY.

DATEVALUE and VALUE functions

Sometimes the problem is not caused by a delimiter, but has a cumbersome date structure just because it's stored as text.

Below is a list of data in different structures that we all recognize as dates. Unfortunately, they were saved as text and need to be converted.

 Data Saved as Text

For these scenarios, converting using various techniques is straightforward.

I wanted to mention two features to handle these scenarios. They are DATEVALUE and VALUE.

The DATEVALUE function converts text into a date value (probably expected), while the VALUE function converts text to a common numeric value. The differences are minimal.

In the figure above, one of the values ​​also contains time information. This is a demonstration of the slight differences in features.

The following DATEVALUE formula converts each value to a date value.


 DATEVALUE function to convert to date values ​​

Notice how the time was removed from the result in row 4. This formula returns only the date value. The result must still be formatted as a date.

The following formula uses the VALUE function.

  = VALUE (A2) 

 VALUE function to convert text to numeric values ​​ [19659003] This formula produces the same results except in row 4, where the time value is also preserved.

The results can then be formatted as a date and time, or as a date to hide (but not remove) the time value. ,

Source link