Importing dates from tab delimited file

[Mike Varley]

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

[#2 · Accepted 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

Result is datetime

[#3]

@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:

Settings:

Result:

Error:

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

Thanks

Mike

[#4]

@Mike Varley 

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

[#5]

@Sergei Baklan 

Hi Sergei

Yes, working now.

Many Thanks

Mike

[#6]

@Mike Varley 

Mike, you are welcome