Sep 24 2020 09:26 AM
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
Sep 24 2020 09:54 AM
SolutionLegacy 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
Sep 24 2020 10:21 AM
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
Sep 24 2020 10:37 AM
You shall use locale of origin (in your case US), not one into which you'd like to transform (I guess UK).
Sep 25 2020 02:00 AM
Sep 25 2020 05:09 AM
Mike, you are welcome
Sep 24 2020 09:54 AM
SolutionLegacy 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