SOLVED
Home

Incorrect date format when using text to columns

Highlighted
Craig Shankland
New Contributor

Incorrect date format when using text to columns

I've got an issue when I am importing a text file and using the text to columns function where its not recoginsing the correct date format in one of the columns.  The text file is generated from a system that is based in North America so the date follows the mm/dd/yyyy format.  As I am in the UK I need it to be dd/mm/yyyy.  For some reason it is able to convert to the correct format for those dates where the day is less than or equal to 12 but when it goes over that it doesnt know what to do with it and just leaves it as text.  If one of my collegues in North America carries out the same process it converts them all ok, I'm thinking its a regonal thing but not sure where to start checking.  I've attached 2 files, one is the raw txt file, the other is after I import it to Excel.  Does anyone have a suggestion as to what the issue might be?

7 Replies

Re: Incorrect date format when using text to columns

Hi Craig

 

On third step of the wuzard select correct date format for that column (US one in your case)

DateFormat.JPG

 

Re: Incorrect date format when using text to columns

Hi Sergei - I've tried this method also but it still ends up with the same results.  It converst the ones with days less than or equal to 12 and ignores the rest !!  Very frustrating.

Re: Incorrect date format when using text to columns

Craig, could you please attach text file (or part of it) from which you import?

Re: Incorrect date format when using text to columns

Here you go....I've had to change the extension from TXT to csv as it wont let me upload a txt one.

Solution

Re: Incorrect date format when using text to columns

Craig, it looks like due to time added to that field. If time is not important for you (i.e. it always 0:00:00) you may open csv file and Text to Columns last column with space separator, remove time only column after that.

Date01.JPG

Date02.JPG

See result attached. If the time is important will find somethin else.

 

 

Re: Incorrect date format when using text to columns

Well it didnt work exactly the way that you thought it would but I've managed to find a solution.  Unfortunatley I havent been able to find the route cause of the problem so will have to carry out this work around every time I need to carry out the same action.  Thanks for your help Sergei.

Re: Incorrect date format when using text to columns

Craig, you may use Power Query (Get&Transform in 2016). It picks you txt file up and transforms correctly if for this column you use Change type->Using locale transformation.

 

You need to adjust the query only once, perhaps add name of txt file as parameter. After that no additional operations.

Attached is how it looks like, "Import File.txt" for this sample is in the root of C:\

Related Conversations
Problems with formating text within a cell.
Maciej Fox  in  Excel  on
9 Replies
No Assignments Tab in Microsoft Teams
Nick Davies  in  Microsoft Teams  on
24 Replies
Create private channel in a team
Catalin Prata  in  Microsoft Teams  on
45 Replies
List all users' last login date
Jakob Rohde  in  Azure Active Directory  on
3 Replies