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

How To Create A Custom List In Excel   A Custom List in Excel is very handy to fill a range of cells with your own personal list. It could be a list of your team members at work, countries, regions, phone numbers or customers. The main goal of a custom list is to remove repetitive work and manual errors i...
Fix Excel Hyperlinks to a Named Range Hyperlinks in Excel must be one of the funkiest features that I love playing around with! They allow you to create interactive buttons within Excel (without the need to create a Macro) and you can make them take you to any cell or range within your Excel worksheet. One shor...
Drop Down List with Data Validation One of the coolest features of Excel is to create a drop down list with your data.  It is one of the first things that I learned and use on a daily basis. You can create a drop down list to eliminate manual entry and re-entry of data, as well as reduce data entry mistakes if y...
Excel Paste Special: A Values Multiplier Paste Special has a few different Paste features and operations that many users do not know about. One of them is the Paste Special > Values > Multiply combination. What that does is multiply a range of selected cells by a value, called the multiplier.  So if you want...