Whenever you import data from your company’s server, ERP system or any other source for that matter, the Dates usually come in a TEXT format.

I will show you a cool trick where you can turn the TEXT Date to a values Date that Excel can read and work with.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Let’s confirm that the Dates are in TEXT format by including the ISTEXT function.

A TRUE means that it is a TEXT format:

istext function

STEP 2: Now that we know that our Dates are in TEXT format we can select the Dates column

select

STEP 3: Go to Data > Text to Columns > Finish

text to columns

This will turn the TEXT Date into an Excel Date!  How quick was that?!?!?!

final

If this does not fix your dates then you can use other methods to clean up that sticky, stubborn TEXT date:

Text To Columns: Dates

Turn Text To Values With Paste Special Values

Top Excel Data Cleansing Techniques

 

Text to Columns - One Click

THIS TIP WAS INSPIRED BY JORDAN GOLDMEIER ON THE FOLLOWING EXCEL TV EPISODE:

exceltv

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Custom Date Formats in Excel Custom date formats in Excel allow you to display only certain parts of the date. Say you had a date of 18/02/1979, which coincides to be my birthday 🙂 You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the...
Fix Incorrect Formatting Using Flash Fill In Excel   Flash Fill in Excel is a new feature that was introduced in Excel 2013.  One of the cool uses of Flash Fill is to fix incorrect formatting in your text automatically. Ever had the scenario where your data is formatted differently? Example: First names starting wit...
Find Blank Cells In Excel With A Color In Excel you can have a data set that comes from an external source which isn't always formatted to your liking. One of the most common things you may encounter are blank cells in your Excel data which can hinder your analysis, especially if you are using a Pivot Table to anal...
How to Calculate Percentage in Excel In calculating percentages in Excel, there are a lot of ways that you could do this: What is the percentage of a number? What is the percentage change of value i.e. The percentage increase or decrease What is the proportion against a total value? Let us go over ...