Formatting Date from Excel

[Michaelkoh89]

Good day everyone. I have extracted a list of dates into excel. These are extraact fromour company's Tableau reporting system. 

However, the dates listed are formatted in "General" as shown. I have tried using text to column and it does not seem to work. Have also tried using "=Datevalue" as well. But that does not work either. 

I have tried splitting the text to column into  3 columns, "June" "19" 2001"  but while trying to use the formula "=date" selecting the 3 cells gives me #value which I think is most likely from the "June" column as that is not numerical. 

Could i seek some help on this? Thank you so much! 

 

[#2]

@Michaelkoh89 Text to columns works for me. Did you set the column data format in step 3 to Date and MDY? For me, this turns the text into a real data value once I press Finish.

[#3]

@Riny_van_Eekelen Hi Riny, 

Yes i did previously and it didnt get anything.

It was worth noting that i selected only the first date previously.

This time i selected the entire column and its interesting to see the results. It seems the first and the bottom September 30th did not convert.

 

[#4]

@Michaelkoh89 I suspect that you have leading or trailing spaces (or both) in the affected dates. Use TRIM to clean out the date column first. Copy/paste values and then do the TTC.

Edit:

Played around a bit. Even with "clean" texts TTC doesn't transform September dates after the 9th  and it does not transform any texts starting with "June" at all. But When I use "Sep" and "Jun", all gets transformed to proper dates by TTC. Don't really know why.

[#5]

Thats really strange. Do you think a manual workaround will be to use the Replace Function to replace the text September and June with Sep and Jun? A very manual workaround tho

[#6]

@Michaelkoh89  Try this in H4:

=LEFT(G4,3)&MID(G4,FIND(" ",G4),10)

and copy it down as far as needed. Copy/Past values and then TTC.

Can't think of an easier way.