Convert Text to Dates with Flash Fill - Excel Data Cleansing Challenge

In this video I explain how to convert dates stored as text to date values that Excel recognizes using Flash Fill. There are many ways to go about this task, so I'm presenting this as a data cleansing challenge to you. I'd like you to share your method for converting this text to a date in the comments below.

This question was submitted by Mark, a member of my Pivot Ready Course. He was a column of dates in his data set that are stored as text.

Excel does NOT recognize the values as dates, and therefore Mark is not able to sort the column or use it with the date grouping feature of a pivot table.

So we must first convert the cells to date values that are recognized by Excel. I share one solution to this problem in the video using the Flash Fill feature of Excel.

Flash Fill was introduced in Excel 2013 for Windows, and it's a great tool for data cleansing tasks like this.

Of course there are many other ways to solve this problem. Please leave a comment below with your solution. I will then create a follow-up video and explain the suggested solutions.

Thank you! :-)

Related articles & videos:

[ExcelCampus]

Please leave a comment below with a technique you would use to convert this text to a date value. There is no wrong answer here, so please feel free to share. I will then create a follow-up video with the suggested solutions. Thank you! :-)

[Ayubgn]

This was great! I appreciate you a million times. I have been looking for this for a while now

[Brownsugar]

Thank you so very much, I was trying to figure out the issue for days. This helped me immensely.

[fboima]

You are such an excel wizard. I learned in a few days what I took years to put together by watching 3 of your videos over and again.

[tomekdluzniewski]

Great tip on Flash Fill. I tried it and it works with some tweaking of the date format (my default is different than US MM/dd/YY). However, if some the dates in the first column were changed to a different year (I tried 2012) the conversion ended up with all dates moved to 2017.  In such case it may be easier to parse the dates using either left/mid/right functions or doing conversion of text to columns, then combining the parts into the date.

[digitalfingerprints]

Thanks for the video. I have never used the Flash Fill functionality, but will in the future.
My first thought on your challenge was to use the Text to Columns under Data. I have used this previously on many occasions. Looking at the data set in your example you have a pattern of Day, Month and Day numbers which are all 3 digits and could easily be moved to separate columns. Then the time stamp (8 digits) which could also be pared to another column. The end result might not be exactly what you want at that point.
But it could be possible to also use Find and Replace: Find the first three digits and replace them with nothing; then Find and Replace all instances of CDT and replace them with nothing; then format the remaining date and timestamp accordingly.

[DougHExcel]

Flash fill is a time saver...thanks for the video!

[jamiecureton]

Thank you that worked for me! and saved me god knows how many hours

[dslabicd]

Great instructional video. We can use the free Office Online Excel to accomplish this, too.

[alasdairsphotoblography]

Flash fill worth the video alone thanks

[dezdsouza]

Excel would not read converted bank statement dates e.g. used your suggestion and voila, in a matter of seconds i could change the entire column of unreadable dates and also back date to the year 2019. thank you!

[perfectvisit]

Or select the entire row/Format cells/Custom and choose and/or fill out the items you want to show in the cells.

[perfectvisit]

Format cells - custom - fill out or remove the items you want or dont want

[Drkalaamarab]

Hi There, many thanks for the excellent description. I am learning excel through your tutorial . I have question here if we want to do any automation how can it be done?

To begin with any small example.
I have query, there is dataset includes start date and end date of multiple passengers. I need to find unique total number of travel days of the passenger and this is continuous process .

[LouSchweichler]

What about the TimeZone and/or Daylight Savings Indicatior? shouldn't there be a new Column or Columns for each: "TimeZone" and Daylight Savings Y/N" ???

[leaderemad]

I would use = mid or left to separate the date

[clarehorne]

I would have just taken the initial data and used "Text to Columns", selecting "Fixed Width", and then moving the lines to collate the data together as you see fit.

[Itisme]

How will change the mixed data which have the date and month places are in different places

[ExceliAdam]

Because it's only 2017 and now is 2017 then this formula works:
=MID(A2, 5, 15)+0

[RafatMkhan]

When I type the date and enter in the another column it remain still as text not changing for me