Convert Text to Dates with Flash Fill – Data Cleansing Challenge
Bottom line: Learn how to convert dates stored as text to date values in Excel using Flash Fill. There are many possible solutions to this problem, so please leave a comment below explaining the technique that you would use.
Skill level: Intermediate
Live Training on Your Suggestions
Download the Excel file
Download the Excel file to follow along and complete the challenge.
Text To Date Challenge.xlsx (104.6 KB)
Text To Date Challenge - Live Training.xlsm (349.8 KB)
Data Cleansing Challenge
Mark, a member of The Pivot Ready Course, had a great question about converting text to dates. He has a data set exported from a system that contains a column with a date & time. Excel does not recognize these cells as a date data type. Therefore, he cannot sort the column or use it with the date grouping feature of a pivot table.
Here is the text for the date that you can copy/paste to Excel. You can also download the example file above.
Tue Mar 14 19:09:37 CDT 2017
So, how do we convert the text into a date that Excel recognizes?
There are several ways to go about this task in Excel. In the video above I share one solution using Flash Fill. I also explain how to determine if Excel recognizes the value as a date, so let’s look at that first.
How To Tell If The Cell Contains A Date
We first need to determine if Excel is recognizing the value in the cell as text or a date.
Method #1: Number Format Drop-down Menu
Here’s an easy way to figure it out the data type of the cell using the Number Format Menu:
- Select the cell.
- Click the Number Format drop-down on the Home tab of the ribbon.
- If the preview of each number format has the same value as the cell contents, then it is Text.
- If the preview changes for number format, then the cell is recognized as a Date.
The image below shows what we will see if the value is stored as Text.
And here is an image of the cell value that is recognized as a date.
Checkout my article and video on Dates in Excel to learn more. This is a very important concept that will help you cleanse your data and work with dates.
Method #2: Filter Drop-down Menu
Another way to see the data type is by using the filter drop-down menu.
In the image below you can see the filter list on the left is all text values. The column for the filter list on the right contains dates, and the date values in the filter list are grouped into Year, Month, Day, Minute, Hour, Second.
If you have some text at the bottom of the grouped dates, then those values will need to be converted to dates to use all the grouping features of a pivot table.
Convert Text to Dates with Flash Fill
Our challenge is to convert the text to a date value. There are several ways to solve this problem, and I’m looking forward to seeing your solution in the comments below.
One simple way is to use the new Flash Fill feature. Flash Fill was introduced in Excel 2013 for Windows, and it’s a really handy tool for data cleansing tasks.
Here’s how to use Flash Fill to extract the date and time out of each cell:
- Select a blank cell to the right of the cell that contains the date stored as text. If the adjacent cell isn’t blank, insert a blank column or copy the column to a blank sheet.
- Type the date that is in the cell and press Enter. In the example in the video I typed the following into the cell: Mar 14, 2017
- Click the Flash Fill button on the data tab of the Ribbon. The keyboard shortcut for Flash Fill is Ctrl+E.
- Flash Fill will find the date in each cell in column A and fill it down column B.
Checkout the video above to see it in action.
We can use the same technique to extract the Time into column C.
- Double-click cell A2 to edit it, select the text for the time, and copy it.
- Paste the time in cell C2. Excel automatically recognizes this as a time value.
- Press Ctrl+E or the Flash Fill button to fill the entire column with the times from column A.
Finally, we might want to combined the date and time into one column. We can do with a formula in column E that sums the date and time value from B2 & C2.
Excel should automatically change the number format to a date & time format. If you see a number like 42808.80, then you can change the number format to view it as a date and time. See the video above for details.
Checkout my article and video on how dates work in Excel to learn more about how dates are numbers formatted as dates.
How Would You Convert This Text to a Date?
There are many ways to go about this challenge in Excel, and I want to know how you would solve it. Please leave a comment below with the technique you would use.
There are NO wrong answers here, and this will be a great opportunity to learn from everyone, so don’t be afraid to share your answer. 🙂
I will create a follow-up video with some of the most common responses. Thank you!