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

Watch on Youtube & Subscribe to our Channel

Live Training on Your Suggestions

Watch on Youtube

Download the Excel file

Download the Excel file to follow along and complete the challenge.

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.

Challenge - How to Convert Text to Dates in Excel
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:

  1. Select the cell.
  2. Click the Number Format drop-down on the Home tab of the ribbon.
  3. If the preview of each number format has the same value as the cell contents, then it is Text.
  4. 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.

Number Format Drop-down Preview Same Date Stored as Text

And here is an image of the cell value that is recognized as a date.

Number Format Drop-down Preview Different Date Value

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.

Filter Drop-down List Text versus Date Values

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.

text-and-error-values-are-listed-below-date-groups-in-the-filter-drop-down-menu

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:

  1. 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.
  2. 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
  3. Click the Flash Fill button on the data tab of the Ribbon.  The keyboard shortcut for Flash Fill is Ctrl+E.
  4. Flash Fill will find the date in each cell in column A and fill it down column B.
Convert Text to Dates with Flash Fill

Checkout the video above to see it in action.

We can use the same technique to extract the Time into column C.

  1. Double-click cell A2 to edit it, select the text for the time, and copy it.
  2. Paste the time in cell C2.  Excel automatically recognizes this as a time value.
  3. 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.  I also have an article on how to get the day name for a date

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!

105 comments

Your email address will not be published. Required fields are marked *

  • The Flash Fill is clearly the most elegant (and fastest). Another method:
    1. Use Text to Columns to get separate columns for each “word:” Tue Mar 14 19:09:37 CDT 2017
    2. Use concatenation to create a “pseudo-date” (one that looks like a date but is text): =B2&” “&C2&”, “&F2 = Mar 14, 2017
    3. Multiply the result by 1 to convert it into a date. (Excel usually takes numbers formatted as text that are used in a formula and converts them into numbers.)
    4. Time converts directly. Add the date and time columns and format as you describe.

  • This was an opportunity for me to test my Power Query knowledge so I went for it.

    1. Load the table into the Power Query Editor.
    2. Promote the first row as header
    3. Split the column into columns by “space” delimiter
    4. Delete the columns not needed (day names and CDT columns)
    5. Move year column (2017) left to shift time column to the right
    6. Select remaining columns and merge to get desired result.
    7. Close and load.

    • Love this solution! Fastest one for me. I have Excel 2010 so don’t have the flash fill feature, but using Power Query seemed so much faster than the Flash Fill option. I was finished in just about 1 minute following your steps.

  • Jon,

    Excellent source of information I will use this method for my job, to save time.
    Does excel calculate the day of week when entered the date???

  • I did not want to use a separate table for lookups. Originally parsed out step by step in columns, but then reduced to this in a single cell:

    with the original text in cell A4

    DATEVALUE(TEXTJOIN(” “, TRUE,(LEFT(RIGHT(A4,LEN(A4)-4),SEARCH(” “,RIGHT(A4,LEN(A4)-4),5))), “,”,RIGHT(RIGHT(A4,LEN(A4)-4),4)))+TIMEVALUE(MID(RIGHT(A4,LEN(A4)-4),SEARCH(” “,RIGHT(A4,LEN(A4)-4),6)+1,8))

    This gives an excel date-time value, which formatted with “m/d/yy h:mm:ss AM/PM” gives the display desired.

    Until this, I never appreciated how useful it is that the time part of a date-time is a decimal value, so could just be added to the date part.

  • =MID(REPLACE(A1,11,,CONCATENATE(“, “,RIGHT(A1,4))),5,21)

    Goes from: Tue Mar 14 19:09:37 CDT 2017
    To: Mar 14, 2017 19:09:37

    Then: =DATEVALUE(LEFT(B1,12))+TIMEVALUE(MID(B1,14,8)) with the correct date format for the cell, 3/14/17 7:09:37 PM

  • Great brain teaser, thanks! 😉
    My answer is maybe not the fastest one, but it works:
    1. column – MONTH =VLOOKUP(MID(A2,5,3),Months!A:B,2,0). I’ve created one additional sheet “Months” with the names of months in one column and numbers in another
    2. column – YEAR /=RIGHT(A2,4)*1/
    3. column – DAY /=MID(A2,9,2)*1/
    4. column – DATE /=DATE(C2,B2,D2)/
    5. column – TIME /=MID(A2,12,8)*1/
    6. column – Converted Date & Time /=E2+F2/
    7. column – Check /=VLOOKUP(A2,Explanation!A:B,2,0)-G2/ and all the results were zeros, yeeees! 😉

  • Great brain teaser, thanks! 😀
    My answer is maybe not the fastest one but it works:
    Column B: MONTH /=VLOOKUP(MID(A2,5,3),Months!A:B,2,0)/ I’ve added additional sheet “Months” with names in one column and numbers in another
    Column C: YEAR /=RIGHT(A2,4)*1/
    Column D: DAY /=MID(A2,9,2)*1/
    Column E: DATE /=DATE(C2,B2,D2)/
    Column F: TIME /=MID(A2,12,8)*1/
    Column G: Converted Date & Time /=E2+F2/
    Column H: Check /=VLOOKUP(A2,Explanation!A:B,2,0)-G2/ I’ve checked all the results with “Explanation” sheet and got all zeros, yeeeessss! 😀

  • I had an extra problem using the Dutch version of Excel: the name of the month is not recognized!
    I first used text to columns (fixed width) to separate, skipping the name of the day and CDT.
    Then I moved the column with the time to be the last column.
    Result:
    A B C D
    2 Mar 14 2017 19:09:37
    3 Jun 10 2017 03:21:15
    etc.

    Then I made a table for numbering the names of the months in 2 columns
    M N
    1 Jan 1
    2 Feb 2
    3 …

    Then I used the DATE-function to create a date from the Year-column, the Month-nale (using Vlookup to change the name into a value) and the Day-column.
    =DATE(C2;VLOOKUP(A2;@M$1:$N$12;2;FALSE);B2)
    I copied that column down for the whole table.

    Select the column with the created dates, copy it, paste values in the same place.
    Then delete the columns A,B and C and the lookup table.

    • EDIT… I forgot the +0
      =REPLACE(MID(SUBSTITUTE(A2,”CDT “, “”),5,99), 7,0,”, “&RIGHT(A2,4))+0

      And not to mention, apply custom format to the result

  • Hi,
    Here is one solution, which does not depend on regional settings:
    =DATE(RIGHT(A3,4),MATCH(MID(A3,5,3),{“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”},0),MID(A3,9,2))+VALUE(MID(A3,12,8))

    Match gives you an index number based on the three letter found in text from the 3rd to the 5th position defined by MID.
    Cheers
    Attila

  • (Excel 2003)

    First, I extracted day, month, year, time:

    “DAY” (B2) =VALUE(MID(A2,9,2))
    “MONTH” (C2) =MID(A2,5,3)
    “YEAR” (D2) =VALUE(RIGHT(A2,4))
    “TIME” (E2) =MID(A2,12,8)

    Next, calculated the date and time values:

    “DATEVALUE” (F2) =DATEVALUE(CONCATENATE(B2,”-“,C2,”-“,D2))
    “TIMEVALUE” (G2) =TIMEVALUE(E2)

    Finally, combined the two:

    “DATE & TIME” (H2) =F2+G2

    Apparently, there could have been less formulas but I prefer small steps at a time.

  • (Excel 2003)

    First, I extracted texts for day, month, year, and time:

    “DAY” (B2) =MID(A2,9,2)
    “MONTH” (C2) =MID(A2,5,3)
    “YEAR” (D2) =RIGHT(A2,4)
    “TIME” (E2) =MID(A2,12,8)

    Next, calculated the date and time values:

    “DATEVALUE” (F2) =DATEVALUE(CONCATENATE(B2,”-“,C2,”-“,D2))
    “TIMEVALUE” (G2) =TIMEVALUE(E2)

    Finally, combined the two:

    “DATE & TIME” (H2) =F2+G2

    Apparently, there could have been less formulas but I prefer small steps at a time.

  • Months

    Jan 1
    Feb 2
    Mar 3
    Apr 4
    May 5
    Jun 6
    Jul 7
    Aug 8
    Sep 9
    Oct 10
    Nov 11
    Dec 12

    =LEFT(A10;10)&” “&RIGHT(A10;4)
    =(MID(A2;12;8))*1
    =((MID(B2;9;2)&”/”&VLOOKUP(MID(B2;5;3);Months;2;0)&”/”&RIGHT(B2;4))*1)+C2

  • Flash fill is fine, but with these data it works. But if there are more years it will not work. Change some of the years into 2016 of 2018 and it will not work….

    Workaround:
    Start with typing the year, then the month and then the day (2017-Mar-14)in a new column.
    Use Flash fill

    End with:
    Select the column and choose Data > Text to Columns. Then only change the last step selecting Date = YMD and it will work.

  • Hi John

    I would personally do something like this:

    =DATE(RIGHT(B3,4),MONTH(DATEVALUE(MID(B3,FIND(” “,B3)+1,3)&” 1″)),MID(B3,9,2))+TIMEVALUE(MID(B3,FIND(” “,B3,10)+1,8))

    and format the cell as:

    m/dd/yy h:mm:ss AM/PM

    Peace.
    Renier

  • I first used the formula =DATEVALUE(MID(A2,9,2)&MID(A2,5,3)&RIGHT(A2,4))+TIMEVALUE(MID(A2,12,8))

    I then had a go with Powerquery which is nice and neat.
    The method I used within PowerQuery was:

    1 convert the raw data to a table (“Tbl_RawData”)
    2 import using Data / From Table Range
    3 Split the data by Delimiter “Space” all occurrences
    4 Merge the new columns containing Day, Month, Year & time columns
    5 Change the new columns data type to Date & Time
    6 Finally, I renamed the Query to “Process raw dates”
    7 “Close and Load to”

    The contents of the Editor is below:

    let
    Source = Excel.CurrentWorkbook(){[Name=”Tbl_RawData”]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Date & Time Stored as Text”, type text}}),
    #”Split Column by Delimiter” = Table.SplitColumn(#”Changed Type”, “Date & Time Stored as Text”, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“Date & Time Stored as Text.1”, “Date & Time Stored as Text.2”, “Date & Time Stored as Text.3”, “Date & Time Stored as Text.4”, “Date & Time Stored as Text.5”, “Date & Time Stored as Text.6″}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Date & Time Stored as Text.1”, type text}, {“Date & Time Stored as Text.2”, type text}, {“Date & Time Stored as Text.3”, Int64.Type}, {“Date & Time Stored as Text.4”, type time}, {“Date & Time Stored as Text.5”, type text}, {“Date & Time Stored as Text.6″, Int64.Type}}),
    #”Merged Columns” = Table.CombineColumns(Table.TransformColumnTypes(#”Changed Type1″, {{“Date & Time Stored as Text.3”, type text}, {“Date & Time Stored as Text.6”, type text}, {“Date & Time Stored as Text.4”, type text}}, “en-GB”),{“Date & Time Stored as Text.3”, “Date & Time Stored as Text.2”, “Date & Time Stored as Text.6”, “Date & Time Stored as Text.4″},Combiner.CombineTextByDelimiter(” “, QuoteStyle.None),”Merged”),
    #”Changed Type2″ = Table.TransformColumnTypes(#”Merged Columns”,{{“Merged”, type datetime}}),
    #”Removed Columns” = Table.RemoveColumns(#”Changed Type2″,{“Date & Time Stored as Text.1”, “Date & Time Stored as Text.5″}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“Merged”, “Date & Time”}})
    in
    #”Renamed Columns”

  • Reading through these responses inspired me to think about this problem more, and I came up with what I think is a very elegant solution to this problem. Since we know that these dates are a fixed length we could do something like this (assume that this formula is written in cell C2 for the first row of data and can then be auto-filled to the bottom):
    =RIGHT(REPLACE(A2,11,13,”,”),12)+MID(A2,12,7)

    Because you use the “+” operator, you don’t even have to convert the range with any “*1” or any of that business. AND, It would even be possible to make this solution work with FIND() or SEARCH() for text date-timestamps that are not fixed length!

    Thanks everyone for the inspiration!!

  • I did Text to Columns since all the dates were fixed length.

    Column 1, Row 2 = Tue
    Column 2, Row 2 = 14-Mar
    Column 3, Row 2 = 19:09:37
    Column 4, Row 2 = CDT
    Column 5, Row 2 = 2017

    Column 7 = ‘=TEXT(B2,”mm/dd”)&”/”&E2 RESULT is 03/14/2017
    The Time stands alone in column 3

  • Went with different idea uses current year only but it seems to work

    Highlight column A
    DATA
    Text to Columns
    Fixed Width
    Next
    Remove or move lines
    leaving line on left side of month
    leaving line on right side of time
    This leaves three columns Click Next
    1st column mark do not import
    3rd column mark do not import
    Click Finish

    Highlight column A
    Home Tab format cells
    Custom
    Select or Type
    [$-409]m/d/yy h:mm:ss AM/PM;@

    Rename A1 if needed
    Done

  • Hi Jon,

    I tried with a VBA approach:

    Sub ConvertTime()

    ‘ Set data range
    Dim rg As Range, c As Range
    Set rg = Range(“A1”).CurrentRegion

    ‘ Move through range
    For Each c In rg.Cells

    ‘ Add value to variable
    Dim sTime As String
    sTime = Range(“A” & c.Row).Value

    ‘ Split into array
    Dim vArrTime As Variant
    vArrTime = Split(sTime, ” “)

    ‘ Get date and time from array
    Dim sMyTime As Date
    sMyTime = vArrTime(5) & “-” & vArrTime(1) & “-” & vArrTime(2) & ” ” & vArrTime(3)

    ‘ Print date and time and format it
    With Range(“B” & c.Row)
    .Value = sMyTime
    .NumberFormat = “yyyy/mm/dd hh:mm:ss”
    End With

    Next c

    End Sub

  • Thanks for the “homework” assignment; it got me back to my Excel training. I wanted to solve it using my limited knowledge without looking anything up. So I did it in a totally convoluted backward way that I could not even begin to explain here (it involved 19 columns), but in the end I got an automated worksheet that allowed me to just copy the first row down. But it was a good exercise and forced me to think in a logical way. When I looked at the much easier ways that other were able to do it, it proved that although there is more than one way to skin a cat in Excel, it’s best to know the shortest way!

  • Jon,

    First I used Power Query similar to Ali Kassie.

    Then I used the following formula:
    =DATEVALUE(MID(A2,5,6)&”, “&RIGHT(A2,4))+TIMEVALUE(MID(A2,12,8))

    change formatting to Custom m/d/yyyy h:mm AM/PM.

    Thanks for the challenge!

    Mt

  • Hi Jon,

    First I tried Power Query similar to Ali Kassie.

    Then, since I like formulas, I placed the following:
    =DATEVALUE(MID(A2,5,6)&”, “&RIGHT(A2,4))+TIMEVALUE(MID(A2,12,8))

    change formatting to Custom m/d/yyyy h:mm AM/PM

    Thanks for the challenge!

    Mt

  • Thks Jon for this tips.
    But if one of the year is other than 2017 (say 2016), it doesn’t take year (2016) when using flash fill.It reports year 2017 throughout!

    Thks Jon for coming back on this issue.

  • I used:
    =DATE(RIGHT(A2,4),MONTH(DATEVALUE(MID(A2,5,3)&”1″)),MID(A2,9,2)) + TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))

    A bit long but very intuitive, I think.

    • Henk,
      I found at least one shortcut in your formula. Once you have a date, you can add in the text for the time and it will translate it automatically.
      =DATE(RIGHT(A2,4),MONTH(DATEVALUE(MID(A2,5,3)&”1″)),MID(A2,9,2)) + MID(A2,12,8)

    • I like this solution. It was not my intuitive process, so I can add this to possible solutions in the future. I also took it one step further and added the time into the formula.
      =VALUE(MID(REPLACE(A2,11,13,”,”),5,12))+(MID(A2,12,8))

  • I also went woth VBA:

    Function text2datetime(text$)

    jaar$ = Right$(text$, 4)

    maand$ = LCase$(Mid$(text$, 5, 3))

    Select Case maand$

    Case “jan”

    maand$ = “01”

    Case “feb”

    maand$ = “02”

    Case “mar”

    maand$ = “03”

    Case “apr”

    maand$ = “04”

    Case “may”

    maand$ = “05”

    Case “jun”

    maand$ = “06”

    Case “jul”

    maand$ = “07”

    Case “aug”

    maand$ = “08”

    Case “sep”

    maand$ = “09”

    Case “oct”

    maand$ = “10”

    Case “nov”

    maand$ = “11”

    Case “dec”

    maand$ = “12”

    Case Else

    maand$ = “01”

    End Select

    Dag$ = Mid$(text$, 9, 2)

    tijd$ = Mid$(text$, 12, 8)

    datum$ = Dag$ & “/” & maand$ & “/” & jaar$

    text2datetime = DateValue(datum$) + TimeValue(tijd$)

    End Function

  • Jon,

    I would tackle this using a function to pass the date-string variable to an array in a similar manner to the ConvertTime sub procedure suggested earlier.

    Function fConvertTS(sDateString as String) as Date
    Dim arrTS asVariant
    arrTS = Split(sDateString,” “)

    fConvertTS = DateValue(arrTS(1) & ” ” & arrTS(2) & ” ” & arrTS(5))
    fConvertTS = fConvertTS + TimeValue(arrTS(3))

    End Function

  • Looks like I am late to this conversation but…
    I chose Text to Columns because of my limited knowledge of Excel.
    I first selected the entire Column A of data excluding the header
    Clicked on the Data Tab and then Text to Columns
    In Step 1 of the Text to Columns, I selected Delimited and then Next
    In Step 2 I chose “Space” as the delimiter and then Next
    In Step 3 I chose “General”, then selected the 1st column and clicked on “Do not import column (skip)”, then selected the 5th column and clicked on “Do not import column (skip)”, and then Finish. Skipping those two columns just saves space.
    Another option in Step 3 is “Destination”. I chose cell B2 just to preserve the original data while playing but it defaults to the original cell which in this case was A2 and although it does not list all of the cells highlighted below A2 they are included.

    Now I have the month in Column B, day in Column C, time in Column D, and year in Column E.
    I placed the following formula in Column F =SUM(DATE(E2,MONTH(1&B2),C2),D2).

    You then use formatting to to see the date and time however suits you.
    I chose a custom format “dddd mmm, dd yyyy hh:mm:ss” just for fun because it closely simulates the original text.

    Obviously this would not work in those areas that do not recognize 3 letter month abbreviations but that is not part of this challenge.

    After reading through these answers I really like the way Kent approached it. Very simple.

    “Kent – November 16, 2017
    Reading through these responses inspired me to think about this problem more, and I came up with what I think is a very elegant solution to this problem. Since we know that these dates are a fixed length we could do something like this (assume that this formula is written in cell C2 for the first row of data and can then be auto-filled to the bottom):
    =RIGHT(REPLACE(A2,11,13,”,”),12)+MID(A2,12,7)

    Because you use the “+” operator, you don’t even have to convert the range with any “*1” or any of that business. AND, It would even be possible to make this solution work with FIND() or SEARCH() for text date-timestamps that are not fixed length!

    Thanks everyone for the inspiration!!”

  • Hi Jon,

    Regarding Kent’s post on 11/16/17, I’m trying to figure out what the 12 is in his formula =RIGHT(REPLACE(A2,11,13,”,”),12)+MID(A2,12,7). I deleted it to see what would happen and it changes the year from 2017 to 1900. I tried 11 and got an error. I was unfamiliar with the REPLACE and MID formulas so I looked them up and played with them today. Nice to know!
    Thank you.
    Pam

  • I’ve built an amortization table and have dates as column headers.
    1/1/2019, 2/1/2019, 3/1/2019,…

    In another table I have Start Date and End Date columns. I’ve built a formula that essentially says, =IF(AND(1/1/2019 is > Start Date, 1/1/2019 < End Date),"TRUE", "")

    So if it's between the start and end date I want a "TRUE" result.

    This formula works fine when I have my data in just a range. (as you'll see in the file).
    The second I turned that range into a table the formula gave me a false reading (because the dates in the column headers are now acting as TEXT and not DATES, despite me changing the format to date).

    How can I fix this or workaround it?

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter