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

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 and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Live Training on Your Suggestions

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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.

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.

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 104 comments
Ruth Hoskin - November 15, 2017

That’s a useful feature, but sadly not available to me in Excel 2010. If I came across this issue, I would use ‘Text to Columns’ / Delimited / Space, then calculate DATEVALUE([date column]&”-“&[month column]&”-“&[year column])+[time column]. I’m sure there are far cleverer ways, using a single formula based on the original text string, but that would be the quickest way for me!

Reply
Jon Wittwer - November 15, 2017

The data is formatted consistently enough that Flash Fill is a great solution in this case. I would probably use a formula if I wanted to be able to quickly copy/paste other data into the spreadsheet later.

Regarding “How to Tell if a Cell Contains a Date” … here’s what I usually do: Try changing the cell format to the General format using CTRL+SHIFT+~. The General format displays a date value as a number, so if the cell does NOT change to a number then you know that Excel is storing the value as text. You can press CTRL+z afterward to quickly undo the format change.

Reply
Fred - November 15, 2017

Set format for column B to the date format you want and in cell B2 use the following formula and then copy down.
=TEXT((MID(A2,5,6)&”, “&(RIGHT(A2,4))),”m/d/yyyy”)+TEXT((MID(A2,12,8)),”h:mm:ss”)

Reply
Cristian Guay - November 15, 2017

Hi Jon
=VALUE(MID(A2,4,16))

will retrieve date and time info. You then format the column with the date format needed

Cristian

Reply
    Henk - November 17, 2017

    On my computer this gives an incorrect date of 1 March 2014

    Reply
Beatrice - November 15, 2017

Hi Jon !
thank you for your videos 🙂
i’ll have them watched asap for this new challenge

This is the solution I’d have prabably used in case I had to face such pb :

I copied the text to convert in A1 (sheet 1) and named the cell to make it easy to read in my formula (Tue_Mar_14_19_09_37_CDT_2017 )

i then created (wherever) a table (“month_table”) with

month month#
Jan 01
Feb 02
Mar 03
Apr 04
May 05
Jun 06
Jul 07
Aug 08
Sep 09
Oct 10
Nov 11
Dec 12

and wrote the following formula

=DATE(DROITE(Tue_Mar_14_19_09_37_CDT_2017;4);INDEX(Month_table[#Tout];EQUIV(STXT(Tue_Mar_14_19_09_37_CDT_2017;5;3);Feuil1!C4:C15;0);2);STXT(Tue_Mar_14_19_09_37_CDT_2017;9;2))+CNUM(STXT(Tue_Mar_14_19_09_37_CDT_2017;12;8))

where Tue_Mar_14_19_09_37_CDT_2017 is the name of the text to convert

this is the result :
14/02/2017 19:09:37

hope to read all the results
thank you to share all your knowledge with us
beatrice

Reply
Lea - November 15, 2017

Very helpful, Jon! Thanks for this. I also tried text to column (splitting the contents into a new column where each space was) and piecing the parts together, but Excel still did not recognize the cobbled together cell value as a date. I also tried combinations of LEFT, RIGHT, FIND and REPLACE but have not found a solution yet that way.

Reply
Walt - November 15, 2017

Here is my vote for solving this with Power Query.
1. Bring the data into Power Query
2. Split cells by space for each occurrence
3. Merge the Month, Day, Year, and Time columns (ignore the rest)
4. Transform the merged column to Date/Time
5. Remove all the other columns
6. Close and Load to new sheet or existing sheet

let
Source = Excel.CurrentWorkbook(){[Name=”table1″]}[Content],
#”Removed Top Rows” = Table.Skip(Source,1),
#”Split Column by Delimiter” = Table.SplitColumn(#”Removed Top Rows”, “Column1″, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“Column1.1”, “Column1.2”, “Column1.3”, “Column1.4”, “Column1.5”, “Column1.6″}),
#”Merged Columns” = Table.CombineColumns(#”Split Column by Delimiter”,{“Column1.2”, “Column1.3”, “Column1.6”, “Column1.4″},Combiner.CombineTextByDelimiter(” “, QuoteStyle.None),”Date/Time”),
#”Changed Type1″ = Table.TransformColumnTypes(#”Merged Columns”,{{“Date/Time”, type datetime}}),
#”Removed Other Columns” = Table.SelectColumns(#”Changed Type1″,{“Date/Time”})
in
#”Removed Other Columns”

Reply
Bo - November 15, 2017

=TEXT(DATE(MID($A2,24,5),MONTH(DATEVALUE(MID($A2,5,3)&” 1″)),MID($A2,9,2)),”m/d/yy;@”)&TEXT(TIME(MID($A2, 12, 2),MID($A2, 15, 2),MID($A2, 18, 2)), ” h:mm:ss AM/PM”)

Reply
Jim Branden, PMP - November 15, 2017

Jon, Thanks for the challenge. I tried the following formula:
=CONCATENATE(MID($A2,4,7), “/”,RIGHT(A2,2), “ “,MID($A2,(FIND(” “,$A2,FIND(” “,$A2)+1)+3),9))
It failed and I discovered that Excel can’t Concatenate the results of formulas! Boo-hisss ;-(

So I broke up my adroit formula into columns as follows:
Column B: Concatenate text: =CONCATENATE(C2, “/”, D2, ” “, F2) (By the way, I set the format as you did in the video)
Column C: Date MMM-DD: =MID($A2,4,7)
Column D: YY: =RIGHT(A2,2)
Column E: Time start Char: =FIND(” “,$A2,FIND(” “,$A2)+1)+3
Column F: Time actual: =MID($A2,11,9)

Thanks for the fun.

Jim

Reply
Patrícia Sanina - November 15, 2017

Here is my solution (before watching your vídeo):

1st:
Place the original text “Tue Mar 14 19:09:37 CDT 2017″ in cell A3

2nd: Build a month/number correspondence table – Fill range B5:B16 with Jan to Dec months and range C5:C16 with 1 to 12

3rd: Obtain you date (as a number) with the following formula, anywhere on your sheet
=VALUE(RIGHT(A3;4)&”/”&VLOOKUP(MID($A$3;5;3);B5:C16;2;FALSE)&”/”&MID($A$3;9;2)&” “&MID(A3;12;8))

4th: Format your number as a date, choosing “dd/mm/aaaa hh:mm” in Format Cells, Number, Custom

Result: you obtain “14/03/2017 19:09” as a number.

Reply
David Gardner - November 15, 2017

=DATE(RIGHT(A2,4),MONTH(MID(A2,5,3)&”1″),MID(A2,9,2))+TIMEVALUE(MID(A2,12,9))
and then use Special format [$-409]m/d/yy h:mm:ss AM/PM;@

The &”1″ in the Month function fools Excel into correctly converting the 3-letter text into a month value.

Reply
    Tim - November 15, 2017

    We had a similar idea. I tried DATE first and had a problem. Ended up using TEXT instead. I see what you did there to resolve it. Cool.

    Reply
Paul Sexton - November 15, 2017

Using Excel formulea >

=DATEVALUE(MID(A2,9,2)&”/”&MID(A2,5,3)&”/”&RIGHT(A2,4))+TIMEVALUE(MID(A2,FIND(“:”,A2)-2,8))

This converts the text string in cell A2

Copy formula down to include all values in Col A

Reply
Elaine - November 15, 2017

Text to Column worked in Excel 2016. Use Fixced Width & double-click 2nd break line to remove it, so month and day # columns will be together. General will recognize date and time formats.

Reply
Tim - November 15, 2017

I have had a similar issue with a report I’ve been running for years. I solved it with a macro. In this case, it looks like all the dates are a good format for a formula solution, I would normally blunt force convert it with something like this:

=TEXT(MID(A1,5,3) & MID(A1,8,3) & “,” & RIGHT(A1,5) & MID(A1,11,10),”mm/dd/yyyy hh:mm:ss”)

Your solutions are more elegant. Always appreciate learning a new technique.

Reply
Adrian Cristea - November 15, 2017

Not sure if it went through the first time, didn’t receive any confirmation, so sending it again.

Hi Jon,

Solution 1: Split the column using SPACE as delimiter, drop the DayName (first) and Regiional Time (second to last) columns. End Up with Months in A, Day in B, Time in C and Year in D. Use this formula in E: =DATE(VALUE(D2),MONTH(DATEVALUE(A2&”3″)),VALUE(B2))+C2 – send it down, done.

Solution 2: A version of the first solution, but without using Split. Note! It will only work on this format =DATE(VALUE(RIGHT(A2,4)),MONTH(DATEVALUE(MID(A2,5,3)&”1″)),VALUE(MID(A2,9,2)))+VALUE(MID(A2,12,8))

Solution 3: Last solution is using Power Query. There are numerous ways to complete this task in PQ, this is only one way of doing it. I’ve added the Power Query steps, the final step is to load it back in the worksheet.

//Power Query script
let
Source = Excel.CurrentWorkbook(){[Name=”Text”]}[Content],
//Split by Space and rename the columns in the same step
SplitBySpace = Table.SplitColumn(Source, “Date & Time Stored as Text”, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“DayName”, “Month”, “Day”, “Time”, “CDT”, “Year”}),
//Remove unnecessary columns
RemovedColumns = Table.RemoveColumns(SplitBySpace,{“DayName”, “CDT”}),
//Combine Date, Month and Year separated by dash
CombineDayMonthYear = Table.AddColumn(RemovedColumns, “Date”, each [Day]&”-“&[Month]&”-“&[Year]),
//Convert the above column to date and use the step to convert the time column too
ChangedDateTimeType = Table.TransformColumnTypes(CombineDayMonthYear,{{“Date”, type date}, {“Time”, type time}}),
//Combine Date and Time columns
CombineDateTime = Table.AddColumn(ChangedDateTimeType, “DateTime”, each [Time] & [Date], type datetime),
//Keep only the last created column and upload it back to the worksheer
RemovedOtherColumns = Table.SelectColumns(CombineDateTime,{“DateTime”})
in
RemovedOtherColumns
//End of Power Query script

Regards,
Adrian

Reply
Lou - November 15, 2017

=DATEVALUE(VLOOKUP(MID(A2,5,3),Months,2,FALSE) & “-” & MID(A2,9,2) & “-” & RIGHT(A2,4))+TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))

where “Months” range =

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

Reply
robert - November 15, 2017

your solution identifies why your programs are so valuable to me! upon viewing the problem, the solution was obvious to me, VBA! I would have created macros that would have resulted in the same outcome as the flash-fill process. my approach would have taked 20-30 minutes based on my beginners skill level. flash-fill was not even on my radar.
thank you for the time effective, easy solution.

Reply
Lou Schweichler - November 15, 2017

I used a combination of DateValue(), VLookUp(), Mid() and Time()to convert the Text String into a Date value. Then custom formatted the cell to mm/dd/yy hh:mm AM.

Also created TimeZone, and DayLignt Savings Y/N columns to permit user to convert Later if necessary.

Date Column Formula:

=DATEVALUE(VLOOKUP(MID(A2,5,3),Months,2,FALSE) & “-” & MID(A2,9,2) & “-” & RIGHT(A2,4))+TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))

TimeZone Formula:

=MID(A2,21,1)&MID(A2,23,1)

Daylight Savings Y/N Formula:

=IF(MID(A2,22,1)=”D”, “Yes”,”No”)

Results:

Date & Time Stored as Text Date TimeZone DayLight Savings Y/N

Tue Mar 14 19:09:37 CDT 2017 3/14/17 7:09 PM CT Yes

“Months” Range for VLookup: (# column formatted as “Number”)

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

Reply
Rod - November 15, 2017

I used this formula in B1
DATEVALUE(MID(A2,9,2)*1&”/”&MONTH(1&MID(A2,5,3))&”/”&YEAR(MONTH(1&MID(A2,5,3))&”/”&RIGHT(A2,4)))+TIMEVALUE(MID(A2,12,8))
Then Formatted cell as d/mm/yyyy h:mm AM/PM
Then filled down

Reply
Caleb - November 15, 2017

I used Power Query. The benefit would be the ability to add to your base data, and have your corrected format column update with a simple refresh. I think… I’ve only used power query a little bit 🙂

I started by creating a table with the base data, then putting in into power query. After that I:
-Split the column by delimiter “space”. You go from one column to six.
-Remove columns with day(“Sat”,”Mon”) and timezone(“CDT”). Left with four columns.
-Merge month column with date column using “space” separator. You now have three columns.
-Merge month/day column with year column using “comma” separator. You have two columns now.
-Merge date column with time column with “space” separator. You now have a single column.
-Change column to date/time.
-Close and load to workbook.

Apologies if I mixed up any steps or did not make things clear.

Reply
UniMord - November 15, 2017

=DATEVALUE(MID(A2,5,7)&”,”&RIGHT(A2,5))+MID(A2,12,8)

Reply
    Lea - November 15, 2017

    thanks for this solution! I wasn’t familiar with DATEVALUE until now

    Reply
      UniMord - November 20, 2017

      As a side note: Although DATEVALUE() is recommended, in our case, it’s not, strictly speaking, necessary.

      Since we’re adding the time, the result is converted into a date, so, we could have gotten away with:
      =(MID(A2,5,7)&”,”&RIGHT(A2,5))+MID(A2,12,8)

      Reply
    Liz - November 15, 2017

    Love this. I came close to this but just concatenated the time to the end. The addition is what fixes it.

    Reply
    prashant - November 16, 2017

    +1

    Reply
    Adrian Cristea - November 17, 2017

    Unfortunately this would only work on US timezone settings.

    Reply
      UniMord - November 20, 2017

      Since the month in Jon’s example is in English, any solution we use, will be language-specific. I tested with the locale set to UK, and it still works. Which locale are you using?

      Reply
Marie - November 15, 2017

I was also thinking text to columns, but the Flash Fill is a much better method. Thanks for the demo!

Reply
Wing Kin Wong - November 15, 2017

We can also multiply the text date with value 1 using the Paste Special Multiply Option, then format the numbers to display date and time format.

Reply
Andrea - November 15, 2017

I would use the mid formula to get the date and the time formula for the time then add them together to get them in the same cell. I also changed the format to include seconds once added together because the seconds went away.

Date: =MID(A2,5,6)&”, 2017″
Time: =TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))
Combined: =B2+C2

Custom format of combined: [$-409]m/d/yy h:mm:ss AM/PM;@

Although I really like the flash fill and need to start using that more often!

Reply
Luis - November 15, 2017

I used the Mid, Right functions to extract the components. After I had changed column A to a fixed font, I could clearly see that these would work.

Used mid and right functions to extract date components in columns C, D, E, since the data was consistent in structure.

Concatenated these in column F for complete date.

Used mid function for time in column G, then concatenated Date and Time in column H.

Excel recognized the dates and time automatically.

Luis

Reply
Ruth - November 15, 2017

That’s a useful feature, but sadly not available to me in Excel 2010. If I came across this issue, I would use ‘Text to Columns’ / Delimited / Space, then calculate DATEVALUE([date column]&”-“&[month column]&”-“&[year column])+[time column]. I’m sure there are far cleverer ways, using a single formula based on the original text string, but that would be the quickest way for me!

Reply
Jan - November 15, 2017

Here is one way using formulas
B2 – Extract the date in A2
C2 – Extract the time in A2
D2 – Add the values together

Cell Formula Results
A2 Tue Mar 14 19:09:37 CDT 2017
B2 =(MID(A2,5,6)&”, “&RIGHT(A2,4)) Mar 14, 2017
C2 =MID(A2,12,8) 19:09:37
D2 =VALUE(B2)+VALUE(C2) 3/14/17 7:09:37 PM

Reply
Kristen Korbler - November 15, 2017

I am a text to columns believer myself, but this definitely seems faster! I actually had a very similar problem at work recently where a Benefits census put birth dates in yyyymmdd and they were all stored as text. Trying to convert to a number did not work, so I used t2c and concat to make a new column with proper dates. Using this video, I went back to see if this (faster/easier) method would have worked.

It brought in the year and day perfectly, but it put the same month on all even though I made sure to use a date without leading zeros. Any ideas/suggestion?

19761113 11/13/1976
19570120 11/20/1957
19570217 11/17/1957
19810602 11/2/1981
19811006 11/6/1981
19500610 11/10/1950
19741010 11/10/1974

Reply
    Allen - November 15, 2017

    Kristen, format your original column of yyyymmdd data as text, and your flash fill will work properly.

    Reply
      Allen - November 15, 2017

      My suggestion was too quick. Formating as text seemed to work, but I had other columns with already parsed data nearby that Flash Fill used to generate a correct date. Oops.

      Reply
      Kristen Korbler - November 15, 2017

      Weird, I am not sure why it worked for you that way. The entire column is already stored as text and still did not work for me

      Reply
Siras - November 15, 2017

Great explanation. I normal use formulas but for current situation should be very difficult to give the solution. Good.

Reply
Hiro Jensen - November 15, 2017

I parse data sent form Java and get the date in the format above. So I’ve been using the code below in VBA (Excel 2010) and thought it was a simple formula to plug into Excel. But…

Interesting how this works in VBA (Intermediate Window):

? DATEVALUE(MID(Range(“A2”), 5, 6) & RIGHT(Range(“A2″), 5))
3/14/2017

But it fails as an Excel formula
=DATEVALUE(MID(A2, 5, 6) & RIGHT(A2, 5))
#VALUE

So I’ll have to use something like this (which is a bit absurd at this point but still works):
=DATEVALUE(MONTH(MID(A2, 5,3)&” 1″) & “/” & MID(A2, 9,2) & “/” & RIGHT(A2, 4))

I could have missed something simple as I didn’t have time to delve into it but I’m still bothered on why it works in VBA and not as a built-in Excel formula.

Maybe you can look into it and explain the discrepancy

Reply
Shay - November 15, 2017

Using Excel 2010, no Flash Fill, came up with this =DATE(RIGHT(A2,4),MONTH(1&MID(A2,5,3)),MID(A2,9,2))

Reply
Michael - November 15, 2017

I would go for Power Query as I still work with EXCEL 2010.

First convert the range of values into an Excel table
name = DateTimeStoredAsText)

and then let PowerQuery magic happen, which is a little bit like TextToColumns:

let
Source = Excel.CurrentWorkbook(){[Name=”DateTimeStoredAsText”]}[Content],
#”Split Column by Delimiter” = Table.SplitColumn(Source,”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″}),
#”Removed Columns” = Table.RemoveColumns(#”Split Column by Delimiter”,{“Date & Time Stored as Text.1”, “Date & Time Stored as Text.5″}),
#”Merged Columns” = Table.CombineColumns(Table.TransformColumnTypes(#”Removed Columns”, {{“Date & Time Stored as Text.3”, type text}, {“Date & Time Stored as Text.6”, type text}}, “de-DE”),{“Date & Time Stored as Text.2”, “Date & Time Stored as Text.3”, “Date & Time Stored as Text.6″},Combiner.CombineTextByDelimiter(” “, QuoteStyle.None),”Date”),
#”Renamed Columns” = Table.RenameColumns(#”Merged Columns”,{{“Date & Time Stored as Text.4”, “Time”}}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}, {“Time”, type time}}),
#”Changed Type2″ = Table.TransformColumnTypes(#”Changed Type1″,{{“Date”, type number}, {“Time”, type number}}),
#”Added Custom” = Table.AddColumn(#”Changed Type2″, “Date/Time”, each [Date]+[Time]),
#”Removed Columns1″ = Table.RemoveColumns(#”Added Custom”,{“Time”, “Date”}),
#”Changed Type3″ = Table.TransformColumnTypes(#”Removed Columns1″,{{“Date/Time”, type datetime}})
in
#”Changed Type3″

Reply
Chris S - November 15, 2017

Correction – forgot the VLOOKUP!
Here we go…
Original date in Cell A2

Put Jan, Feb, Mar…in A13:A24
Formula in B13: =MONTH(DATEVALUE(A13&”1″)) (drag down to B24)
Formula in B2: =MID(A2,5,3)
Formula in C2: =IF(MID(A2,9,1)=0,MID(A2,10,1),MID(A2,9,2))
Formula in D2: =VLOOKUP(B2,$A$13:$B$24,2,FALSE)
Formula in E2: =DATE(RIGHT(A2,4),D2,C2)

E2 will now capture the value in date format.

Reply
Mark Drury - November 15, 2017

Jon, Really handy function I have an issue in Power BI. When I login online rather than the installed version and I have a date column. Within Excel the data is in the correct UK Format i.e 30th May 2017 is shown as 30/5/2017 however when the data is uploaded into Power BI the data then changes to American format. 5/30/2017. I have read articles that start the browser language needs setting for Chrome and for IE to the correct setting which I have done but it still appears in the wrong format. Any help on this from either yourself or a member would really be appreciated. Keep up the great work your tips save loads of time:)

Reply
ThomasVL - November 15, 2017

=DATE(RIGHT(A3;4);IF(RIGHT(LEFT(A3;7);3)=”Jan”;1;IF(RIGHT(LEFT(A3;7);3)=”Feb”;2;IF(RIGHT(LEFT(A3;7);3)=”Mar”;3;IF(RIGHT(LEFT(A3;7);3)=”Apr”;4;IF(RIGHT(LEFT(A3;7);3)=”May”;5;IF(RIGHT(LEFT(A3;7);3)=”Jun”;6;IF(RIGHT(LEFT(A3;7);3)=”Jul”;7;IF(RIGHT(LEFT(A3;7);3)=”Aug”;8;IF(RIGHT(LEFT(A3;7);3)=”Sep”;9;IF(RIGHT(LEFT(A3;7);3)=”Oct”;10;IF(RIGHT(LEFT(A3;7);3)=”Nov”;11;12)))))))))));RIGHT(LEFT(A3;10);2))

Reply
Chris S - November 15, 2017

Here we go…
Original date in Cell A2

Put Jan, Feb, Mar…in A13:A24
Formula in B13: =MONTH(DATEVALUE(A13&”1″)) (drag down to B24)
Formula in B2: =MID(A2,5,3)
Formula in C2: =IF(MID(A2,9,1)=0,MID(A2,10,1),MID(A2,9,2))
Formula in D2: =DATE(RIGHT(A2,4),D2,C2)

D2 will now capture the value in date format.

Reply
Don - November 15, 2017

Like Terrance, my first thought would have been to use text to column, delete the new columns (day of week, CDT), then merge the month, day and year & the time. Way too much manual intervention. The other method I came up with was to use VBA and the split function inside a loop. However, the flash fill, is a great option and less steps than either of the other two I mentioned.

Reply
Michael Franks - November 15, 2017

1. Enter a “1” into a cell somewhere outside the data.
2. Copy the cell with the “1” in it.
3. Highlight all the cells with the text date.
4. Select Paste Special and chose “Multiply”
5. Change date numbers to date format.

This also works for numbers that are in text format.

Reply
Jen - November 15, 2017

Hi Jon,
As always, a great video!
I converted your dates using text-to-columns. If you use “Fixed width”, you can surround the date and time, then skip the other columns. You will be left with just the one column in the correct format.
(I tried to post a screen clipping but wasn’t able.)
Can’t wait for your next video.
Jen

Reply
Ruth H - November 15, 2017

That’s a useful new feature, although sadly not available to me in Excel 2010. If I came across this issue, I would use ‘Text to Columns’ | Delimited | Space. Then calculate DATEVALUE({date column}&”-“&{month column}&”-“&{year column})+{time column}. I’m sure there are cleverer ways, using a single formula based on the original text string, but this would be the quickest way for me!

Reply
Kent - November 15, 2017

Unfortunately, we are stuck on Excel 2007 at my work. I actually run into this issue fairly often, as much of our data is pulled from various database sources and dumped into MySQL, Access or ftp’d as a CSV, TAB or TXT file. And so, we deal with some date format Excel does not recognize several times a day! Your post has made me even more jealous that we don’t have a newer version of Excel at our disposal!

That said, my solution is fairly simple (and especially so in this case as the “strange” date format is uniform in each cell in column A).

REFERENCE CELL (ASSUME THIS IS CELL A2):
Tue Mar 14 19:09:37 CDT 2017

TO RETREIVE DATE PART (ASSUME I’M ENTERING THIS FORMULA INTO CELL B2):
=DATE(RIGHT(A2, 4), MONTH(1&MID(A2, 5, 3)), MID(A2, 9, 2))

TO RETREIVE TIME PART (ASSUME I’M ENTERING THIS FORMULA INTO CELL C2):
=TIMEVALUE(MID(A2, 12, 8)

Then in C2 you could enter: =B2+C2 and format as appropriate.

Or, I might just enter: =DATE(RIGHT(A2, 4), MONTH(1&MID(A2, 5, 3)), MID(A2, 9, 2))+TIMEVALUE(MID(A2, 12, 8)) and format it from there.

I would like to add that, in practice (and my reality), I wouldn’t bother with specific cell references. I would immediately turn my data into an Excel table (CTRL-t) and write the generic formula so that I don’t have to worry about it:
=DATE(RIGHT(Table1[[#This Row],[Date & Time Stored as Text]], 4), MONTH(1&MID(Table1[[#This Row],[Date & Time Stored as Text]], 5, 3)), MID(Table1[[#This Row],[Date & Time Stored as Text]], 9, 2))+TIMEVALUE(MID(Table1[[#This Row],[Date & Time Stored as Text]], 12, 8)).

Finally, as I mentioned, we deal with this daily in my work and though more complex, all of those RIGHT(), MID() and LEFT() formulas can be written in combination with the FIND() formula to get your starting and stopping points, it just requires a little creativity to find what you truly want to find.

Hope that someone finds this helpful!

Reply
Carsten - November 15, 2017

Yes indeed: A very smart solution with the flash fill function.
I would have realized it much more complicated with cutting the data in pieces and rebuild a date and time:
=DATE(RIGHT(A2,4),VLOOKUP(MID(A2,5,3),Table1,2,FALSE),MID(A2,9,3))+MID(A2,12,8)

I used also a lookup table to translate the month to a value:
Jan 1
Feb 2
Mar 3

Dec 12

Reply
Warren - November 15, 2017

Jon – Your solution is elegant. Mine, not so much, but it worked.

First, use text to columns (delimited), and select space as the delimiter. This separates all the components into separate cells. Next, you can join the components together to form a date value. The day and year are fine, but the month abbreviations are not recognized as date values, so I created a small table to convert using Vlookup, mapping each month to its appropriate month value. Then just concatenate the values, add the time, and you’re set.

But I like your method better 🙂

Reply
Stuart - November 15, 2017

We’re still using the antiquated excel 2010, therefore don’t have flashfill.

As long as the cells are all set up in same format (i.e. month / date are in same columns, then I would use Date() in conjunction with Right() / mid() formulae to find the values, with a simple vlookup for month (i.e. Feb = 2, Mar = 3, etc.)

=DATE(RIGHT(A2,4),VLOOKUP(MID(A2,5,3),E2:F13,2,0),MID(A2,9,2))

Again for time range, I would use =time() with mid() ranges.

Alternatively, if cells were of different length, I could use “text to columns” using the spaces as separators, and then build formulae using the independent columns created.

Reply
Danni - November 15, 2017

I would have used a macro with numerous steps to it. Love Flash Fill – have never seen this before. Now it is the only solution that comes to mind! Thanks for the grat info.

Reply
Suz - November 15, 2017

This is a great tip and a real time-saver. Thanks so much.

Reply
Lisa - November 15, 2017

Text to column: Skip the columns for the day of the week and the time zone; reassemble the numbers as you did using cell + cell (making sure to re-order to get dd/mm/yy and time).

Reply
Colin - November 15, 2017

You could do a fixed width text to columns on the first 4 characters to cut off the day of the week, then do a find and replace on “CDT ” to “”. From there excel should recognize what’s left as a date.

Reply
    MF - November 16, 2017

    That may give you incorrect answer when the year is not 2017, as Excel assume current year given the date input in that way.

    Reply
Debbie - November 15, 2017

Super! Thanks for another short, informative session.

Reply
Terence - November 15, 2017

I tend to use text to column but it doesn’t seem work for a format like that (at least in Excel 2010)

Great tip as usual Jon, explained very clearly.

Reply
    Kent - November 15, 2017

    That’s a good idea Terence! I’ve never tried that approach, but in this case you can combine the TEXT-TO-COLUMN and DATE() formula to get the desired result. For example, if you do T-T-C on “Tue Mar 14 19:09:37 CDT 2017” using a ‘SPACE’ delimiter, you get (among others) columns that give you “Mar”, “14”, “19:09:37”, and “2017”, and you can use these specific four columns to generate a date-timestamp that is Excel “worthy”…

    =DATE(“2017″, MONTH(1&”Mar”), “14”) + “19:09:27”

    Would give you the desired approach, where you replace everything in quotes (“”) with the cell or table location reference.

    Reply

Leave a Reply: