How to Convert Text to Date in Excel

Are you struggling to convert text to date in Excel? You’re not alone. This common challenge can thwart even seasoned Excel users, making crucial tasks like date calculations, PIVOT Table groupings, and general reporting a hurdle. In this essential guide, we delve into the steps to convert text to date, transforming your Excel experience and data accuracy.

Introduction

Imagine you’re importing data from an external system into Excel. You anticipate a seamless integration but are met with a snag: your dates, crucial for analysis, are misread as text. This is where the need to convert text to date in Excel becomes evident.

Excel’s default settings often interpret imported dates as text, which can derail your data analysis, reporting, and decision-making processes. This introductory guide is your first step in overcoming these obstacles. Here, you’ll learn not just how to convert text to date in Excel, but also why this skill is invaluable in ensuring the integrity and usability of your data.

Example

A text date in Excel can take on many forms but a common one might look similar to the following, where you just have a string of numbers but Excel considers it to be text:

The format here is DDMMYYYY but Excel has interpreted this as one long text string of 8 characters. Note if you are working with American dates (MMDDYYYY) then the following solution will still apply, you just have to switch days and months around.

Solution

To get the text date into an actual usable date in Excel the DATE function can be applied. The DATE function takes the format:

• =DATE (year, month, day)

Breaking the DATE function down it basically means you have to tell Excel the character positions of the day, the month and the year. As an output the result will be a conversion to a date format that you can use.

A useful technique to adopt here is breaking out the text date to understand where each part is in terms of the character position, you only need to think about this once as long as the text dates are all in the same format:

• DAY – This is found in character position 1 and 2. In order to select character positions 1 and 2 the LEFT() function can be used. This function counts characters from the left, or 1st position, of a text string.

• MONTH – This is found in character position 3 and 4. In order to select character positions 3 and 4 the MID() function can be used. This function counts characters from one position to another. As the positions are always 3 and 4 it can be used to select those two characters in the text string.

• YEAR – The year is the last four characters in positions 5 to 8 (inclusive). The RIGHT() Function in Excel can be used for this as it counts characters backwards from the end of the text string, so in this example it can be used to count back the last four characters in the text.

Putting it all together

Having identified the position of each section of the date (the day, month and year) and understanding how to extract each part using the left, mid and right functions the date can be converted. Assume the date is in cell A2:

• DAY – This is calculated with =LEFT (A2, 2)

• MONTH – This is calculated with = MID (A2, 3, 2)

• YEAR – This is calculated with = RIGHT (A2, 4)

The final step is to put this all together in the DATE function. Remember the DATE function takes the format:

• =DATE (year, month, day)

Therefore combining this all into one results in the formula:

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

The end solution will show that the text has been converted to an actual date:

Common Problem

A common problem with converting text to date in Excel is that your result might show in a numerical format to start with, this is because you have basically performed a calculation. For example you might see the following instead of the lovely dates:

If you see this output there is no need to panic, the format of the cells just need to be amended from General or Number to Date. To do so highlight the cell or cells to change and Right Click on them. Next select ‘Format Cells’ from the options box:

Conclusion

In conclusion, mastering how to convert text to date in Excel is vital for accurate data management and analysis.

This post demonstrates the process using the DATE function, breaking down the text date into day, month, and year components with LEFT, MID, and RIGHT functions. Once combined in the DATE formula, this method effectively converts text to actual date format.

Remember, if the result initially appears numerical, simply adjust the cell format to ‘Date‘. This guide ensures that you can confidently convert text to date in Excel, enhancing your data handling capabilities.

Keep Excelling,

Now that you’ve mastered how to convert text to date in Excel, take your skills a step further. Learn how to transform a date into a month name, enhancing your data presentation and analysis. Dive into our guide ‘How to Convert a Date into a Month Name in Excel‘ for easy-to-follow steps and practical tips. Perfect for creating more reader-friendly reports and data overviews.