Forum Discussion
Formatting Date from Excel
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!
5 Replies
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.
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.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.
