Forum Discussion

Mike Varley

Copper Contributor

Sep 24, 2020

Solved

Importing dates from tab delimited file

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

excel

SergeiBaklan
Sep 24, 2020
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

5 Replies

SergeiBaklan
Diamond Contributor
Sep 24, 2020
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
- Mike Varley
  Copper Contributor
  Sep 24, 2020
  SergeiBaklan
  
  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
  - SergeiBaklan
    Diamond Contributor
    Sep 24, 2020
    Mike Varley
    
    You shall use locale of origin (in your case US), not one into which you'd like to transform (I guess UK).