Date Format Problem in downloaded CSV file

[lbb]

When downloading the csv file of my results into Excel 2016 the Date column [mm/dd/yyyy hh:mm:ss] is aligned for each respective month alternating left and then right.  Eg June 2019 is aligned right and May 2019 is aligned left.   

The dates which are aligned right act as Date fields but the dates aligned left do not act as Date fields.

Have tried:

- aligning all column data to the right

- TRUNC function followed by

   - using Text to Columns to correct the issue

- LEFT function to strip just the date element and not the time - this only works for the right aligned data

 

Having alternate months sorted by alignment is good for initial appearance but it seems to be at the expense of corrupting the data.

Is there a fix that either I can do in Excel, or that TMN could do to in the data presentation to resolve this.?

 

Many thanks.

 

[CA3LE]

Can you post a screenshot or recording of what you're seeing.  There isn't supposed to be any alignment like you're describing.

 

Opening with sheets on Mac

 

Opening with Google Sheets

 

Opening the same file with a raw txt editor shows the true CSV (Comma-separated values) content

 

I think that Excel is doing its own formatting.  Post a screenshot so I can see what you're seeing on your screen.

 

 

Edited June 7, 2019 by CA3LE
minify images

[lbb]

Expanding column A to accommodate the data this is is the screenshot of how excel aligns the date column.

In fact it is not alternate months but seemingly a random split.

Those aligned left are text fields and those aligned right are numeric or date fields.

Of the most recent 200 lines they are split as follows

 

 

 

[Sean]

From a quick check on my end, the issue is that Excel expects dates to be formatted as DD/MM/YYYY (how we write dates in Ireland), but the CSV file has them written as MM/DD/YYYY, the way the USA writes dates.

 

When Excel reads in the CSV file, it read the lines like 06/01/2019 as the 6th January 2019, 8/02/2019 as the 8th February 2019 and so on until it reaches a 13 or higher digit in the middle, such as 05/13/2019.  As there is no 13th month, it will treat this as a text field and in turn left-align them.  This continues until it reaches a line that contains a digit of 12 or less in the middle of the date, which again it reads as a valid month.

 

Microsoft recently changed the CSV importing process in Office 365 (which I use), so these steps below may not correspond with your Excel version.

 

Go into the Data column (1), click 'Get Data' (2), 'From File' (3) and then 'From Text/CSV' (4):

 

Choose the downloaded CSV file, then click 'Transform Data':

 

Right-click the Column1 heading (1), then go into 'Change Type' (2) => 'Use Locale...' (3):

 

Choose 'Date/Time' for the Date Type field (1), then 'English (United States)' for the Locale field (2), then click 'OK':

 

Finally click the "Close & Load" icon (4 in above screenshot) and it should import and present them in your locale date format, e.g. dd/mm/yyyy for my region: ?

[CA3LE]

Thank you for clearing that up Sean, you rock. 

 

I'll add an option to change the date format sitewide.

[lbb]

Sean,  Your solution worked!  Thank you so much for sorting this mystery.  I am in the UK so the date format is the same as for Ireland.   Another slice of Excel knowledge gained by me .

 

CA3LE - Yes please - if you can add the site option for the date format that will make it a whole lot easier for those of us on this side of the pond.

[CA3LE]

Sean saves the day again!  ?