SOLVED

Importing dates from tab delimited file

New Contributor

Hi

 

I have a TAB delimited file that contains date\time info in the following format e.g:

 

mm/dd/yyyy hh:mm PM

 

9/17/2020 5:33 PM

 

I've tried various methods of importing using the legacy text import or the 'transform data' tool but I cannot get Excel to see it as a date and time. Feels like I'm missing something obvious! Can anyone help?

 

version Office 365 Pro plus 1908.

 

Thanks

Mike

5 Replies
best response confirmed by Mike Varley (New Contributor)
Solution

@Mike Varley 

Legacy wizard doesn't convert datetime, but Power Query does. Select the column and from right click menu Change type using locale, here

image.png

Result is datetime

image.png

@Sergei Baklan 

 

Hi Sergei

Thanks for replying. Some progress! Seems to work for some cells but not others even though they look the same...

Input:

Screenshot 2020-09-24 180808.png

 

 

 

 

 

 

 

 

Settings:

Screenshot 2020-09-24 181044.png

 

 

 

 

 

 

 

 

 

 

 

Result:

Screenshot 2020-09-24 181245.png

 

 

 

 

 

 

 

 

 

Error:

Screenshot 2020-09-24 181340.png

 

 

 

I can't see any difference in cell contents. Any idea what causes this?

 

Thanks

Mike

 

@Mike Varley 

You shall use locale of origin (in your case US), not one into which you'd like to transform (I guess UK).

@Sergei Baklan 

 

Hi Sergei

 

Yes, working now.

 

Many Thanks

Mike

@Mike Varley 

Mike, you are welcome