SOLVED

Importing dates from tab delimited file

%3CLINGO-SUB%20id%3D%22lingo-sub-1709313%22%20slang%3D%22en-US%22%3EImporting%20dates%20from%20tab%20delimited%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709313%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20TAB%20delimited%20file%20that%20contains%20date%5Ctime%20info%20in%20the%20following%20format%20e.g%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emm%2Fdd%2Fyyyy%20hh%3Amm%20PM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E9%2F17%2F2020%205%3A33%20PM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20various%20methods%20of%20importing%20using%20the%20legacy%20text%20import%20or%20the%20'transform%20data'%20tool%20but%20I%20cannot%20get%20Excel%20to%20see%20it%20as%20a%20date%20and%20time.%20Feels%20like%20I'm%20missing%20something%20obvious!%20Can%20anyone%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eversion%20Office%20365%20Pro%20plus%201908.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EMike%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1709313%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1709460%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20dates%20from%20tab%20delimited%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F209794%22%20target%3D%22_blank%22%3E%40Mike%20Varley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELegacy%20wizard%20doesn't%20convert%20datetime%2C%20but%20Power%20Query%20does.%20Select%20the%20column%20and%20from%20right%20click%20menu%20Change%20type%20using%20locale%2C%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20556px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F221880i70359DC25C7F13CD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EResult%20is%20datetime%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20671px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F221882i310029D206FAC7FC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted
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

Highlighted

@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

 

Highlighted

@Mike Varley 

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

Highlighted

@Sergei Baklan 

 

Hi Sergei

 

Yes, working now.

 

Many Thanks

Mike

Highlighted

@Mike Varley 

Mike, you are welcome