Hey there Anylearners! During your experience with data cleansing in Excel, I’m relatively confident that you will encounter some problems with the data format and how to convert them. In this tutorial, you will learn how to format text as dates, which is a common issue for beginners in Excel.

More frequently, data is exported directly from different sources, ranging from databases, tables scraped from webpages as well as various financial software. Consequently, some incorrect formatting will occur when the values are imported into Excel.

Therefore, I’ve done the tough work for you and did some research online for the fastest and most effective method for resolving date formats, especially for converting text to date. Most likely, this problem can be solved with many possible solutions. However, using the Find and Replace feature stood out to me as the most intuitive and beginner-friendly.

I will cover this method in an example further in this post. Let us understand more about how the formats in Excel are detected, before proceeding with an example.

Why are my dates formats not recognized?

A common problem encountered by many is having incorrect dates derived from the data not being recognized as a date in Excel. The dataset below shows a typical example of how the dates are not being read as the date format.

In the screenshot image above, the dates shown in Column A are not being recognized as dates. Although it may seem like dates to you, Excel does not register the numbers as dates. Often, these dates were taken from a different table or data environment that recognizes dates differently.

However, a quick tip for noticing this problem would be observing the alignment of the date values. Date, time, currency and other numerical formats in Excel align values to the right of the cell. The text format aligns values to the left of the cell.

In this example, Column A is occupied with data in the Text format. To show these dates properly, these have to be converted into the Date format.

How do I know the format of my dates?

You can do a simple check on your dates by following these steps:

  1. Select any cell with your dates
  2. Select the Home tab
  3. Navigate to the Number section
  4. Select the Dropdown list

The drop-down box shows the format of the current selected cell. As shown in the example above, my dates have the Text format. This means that the Text-formatted data needs to be converted into Date-formatted data.

How to Format Text as Dates?

Now here’s a great trick for quickly solving this simple problem – using the Find and Replace function within Excel! Here’s how:

Convert Text into Dates using Find and Replace

Select the Home tab from the Excel ribbon

Navigate to the Editing section

Click on Find & Select

Select Replace

A dialog box should pop up for the Find and Replace function.

Type “/” or “-” in the Find what field

Use “/” if your date uses the “XX/XX/XX” style
Use “-” if your date uses the “XX-XX-XX” style

Type “/” or “-” in the Replace with field

Use “/” if your date uses the “XX/XX/XX” style
Use “-” if your date uses the “XX-XX-XX” style

Select Replace All

Or use ALT + A as a shortcut

This should convert your dates from text into the date format that we needed!

Of course, there would be multiple ways for this to be done. Personally, this would be the easiest and fastest method that does not require much manual manipulation of the data. The Find and Replace function forces Excel to evaluate all cells that contain the “/” or “-” symbols and assign the correct data format type accordingly.

In conclusion, when trying to convert your text to date format, do check what the format of your dates are and consider using the Find and Replace tool on Excel to fix it. Hope this tutorial helps you!

Useful Shortcut Tip for Find and Replace

  • Use Ctrl + H as a quick shortcut to activate the Find and Replace dialog window.

My Favorite Learning Resources:

Here are some of the learning resources I’ve personally found to be useful as a data analyst and I hope you find them useful too!

These may contain affiliate links and I earn a commission from them if you use them.

However, I’d honestly recommend them to my juniors, friends, or even my family!

My Recommended Learning Platforms!

Learning PlatformWhat’s Good About the Platform?
1CourseraCertificates are offered by popular learning institutes and companies like Google & IBM
2DataCampComes with an integrated coding platform, great for beginners!
3PluralsightStrong focus on data skills, taught by industry experts
4StratascratchLearn faster by doing real interview coding practices for data science
5UdacityHigh-quality, comprehensive courses

My Recommended Online Courses + Books!

TopicOnline CoursesBooks
1Data AnalyticsGoogle Data Analytics Professional Certificate
2Data ScienceIBM Data Science Professional Certificate
3ExcelExcel Skills for Business Specialization
4PythonPython for Everybody SpecializationPython for Data Analysis
5SQLIntroduction to SQLSQL: The Ultimate Beginners Guide: Learn SQL Today
6TableauData Visualization with TableauPractical Tableau
7Power BIGetting Started with Power BI DesktopBeginning Microsoft Power BI
8R ProgrammingData Science: Foundations using R SpecializationLearning R
9Data VisualizationBig Book of Dashboards

To see all of my most up-to-date recommendations, check out this resource I’ve put together for you here.

More Articles For You