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

Sign up for our Excel webinar, times added weekly: www.excelcampu...
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.
Download the example file here: www.excelcampu...
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:
Article on How Dates Work in Excel: www.excelcampu...
Video on How Dates Work in Excel: • Understand How The Dat...

[@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! :-)

[@ahmedraza4981]

Ctrl + H (replace function)
Find value = CDT 2017
Replace with = (keep it blank)
Press Alt + A (Replace All)
It will automatically converted into a date formate.
Try it... Have a nice day, nice video to

[@ExceliAdam]

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

[@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.

[@tomekdluzniewski2085]

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.

[@cdidi3130]

Hi, could you help me? When I tried to follow the steps, Excel did not do what you showed in the video, instead it gives me an error saying "Please double check the FLash Fill results. We encountered some input cells with values longer then 255 characters, and we had to skip them. "
I am trying extract the date from data like "Thu, 01 Aug 2024 20:48:52" to any reconginzable date format (e.g. like '2024-01-08' or '08/01/2024')

[@mrpennington72]

Not a terribly elegant solution, but it works.
=DATE(RIGHT(A2,4),VLOOKUP(MID(A2,5,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,0),MID(A2,9,2)+0)+MID(A2,12,8)+0

[@mrpennington72]

Better version, based upon Tom Urtis pointer on Twitter [Using Month(1&"Jan") to convert to month number]:
=DATE(RIGHT(A2,4),MONTH(1&MID(A2,5,3)),MID(A2,9,2)+0)+MID(A2,12,8)+0

[@Ayubgn]

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

[@dezdsouza5803]

Excel would not read converted bank statement dates e.g. 23Mar.......i 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!

[@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" ???

[@clarehorne9507]

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.

[@Brownsugar5329]

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

[@MarissieV]

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

[@sparkvibe1319]

Thank you big help

[@ExcelCampus]

Happy to help, @sparkvibe1319 😀

[@MarissieV]

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

[@RafatMkhan]

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

[@saintbikerart8987]

Hellow, sir can i ask questions? What if the transaction date/time is
Example: 2024-01-02 00:15:05.900?
I can't change it to, Jan-1-2024

[@ExcelCampus]

Hi Saint! 😊 Try right-clicking the cell, and choosing Format Cells. In the Format Cells window that appears, select the Number tab at the top, then choose Custom as the Category. In the Type textbox, enter mmm-d-yyyy, then click OK. I hope that helps you! 😊

[@anorakfactory]

Excellent !! Will always use this 🙂

[@leaderemad]

I would use = mid or left to separate the date

[@oddnumber8149]

almost all doesnt work for me...how do you achive it?

[@oddnumber8149]

Mar 14,2017 excel doent recognise automatically as a date as a result your video doensnt work

[@jamiecureton2898]

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

[@ExcelCampus]

Great to hear that, @jamiecureton2898 ! 😀

[@kennethjimah5364]

Love it! Thank you. Also subscribed 🙂

[@ExcelCampus]

Thanks for subbing! 😀

[@DougHExcel]

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

[@rnsraja]

Thanks, Good learning

[@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 .

[@9MusicisLife5]

Thank you so much!

[@Itisme177]

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

[@dslabicd2425]

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

[@alirehman9467]

How to make daily weekly and monthly expenses sheet in excel

[@alexrosen8762]

Super solution! Thanks Jon :-)

[@alasdairsphotoblography6115]

Flash fill worth the video alone thanks

[@fboima8140]

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.

[@ExcelCampus]

Thanks Fayia! ;-)

[@zkhater63]

thanks man

[@ma3oth]

Thanks a lot for this :)

[@ExcelCampus]

You're welcome, Moad! 😀

[@Thomas-ys4mc]

7:39

[@puneetvij8433]

Sir John,
Just terrific! ; )

[@ryanwalton7969]

Are you ben dyers uncle????

[@hoomam7500]

Thanks alot ❤

[@ExcelCampus]

You're welcome 😊

[@ryanwalton7969]

Your a cool dude

[@diptipradhan4436]

Wow

[@imranali-iy5wk]

I already know

[@ryanwalton7969]

Are you Ben dyers uncle???