CODEWITHSUNDEEP
excelcsvdate-formatting
Comic Coder
Comic Coder

Reputation: 549

Date format in CSV file strange behaviour

I have a .csv file that is created generated using some legacy software. There is no way to look inside the code at how or what its doing to produce the csv file.

My issue is that the date field format is behaving strangely. The .csv file is storing the date in the following format.

MM-dd-yyyy HH:mm:ss.SSS

Looking in the file in notepad (raw data) we get a list of columns like this:

"01-12-2013 14:23:38.000",146.199997
"01-12-2013 23:20:29.000",146.199997
"01-13-2013 17:59:23.000",152.299988
"01-13-2013 18:08:40.000",147.300003
"01-13-2013 18:25:52.000",156.000000
"01-13-2013 18:35:14.000",146.300003
"01-13-2013 18:44:56.000",148.500000
"01-13-2013 18:56:41.000",151.699997

NOTE: I have stripped out the remaining columns to make it easier on the eye so we are only seeing the first 2 columns in the .csv file.

Now when I open this file in Excel 2010 version 14.0.6023.1000 (32-bit) I get date format problems.

Firstly if I just open the file I get blocks of mixed date formatting like this:

Formatting 1

And this:

Formatting 2

I can see that Excel is getting confused when the day value is switching from 12 to 13. If i then try to change the formatting to fix the issue using the following cell formatting settings:

Column Formatting

Then I get the following displayed in the column when the day is less than 13:

Date Format 3

And the following format when the day is greater than or equal to 13:

Date Format 4

Any help would be really appreciated on this as I am now completely out of ideas.

NOTE: I have also tried copying the contents of the .csv file into another file but the same things occur in the new file.

Upvotes: 3

Views: 14951

Answers (3)

Comic Coder
Comic Coder

Reputation: 549

I found a work around for my instance after reading the answers from @Gary's Student and @hughg. Thanks for the ideas.

So to fix my issue:

  1. Modified my regional settings for dates and times to US.
  2. Opened the file as normal in excel.
  3. Selected the column in question and formatted cells into text. This converted all the dates in the file into floating point numbers.
  4. Finally Apply following custom format to the cells: dd/mm/yyyy hh:mm:ss

The result is that every date in the file is now correctly formatted into UK date format.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96781

You have a conflict between the format of the .csv and your Regional settings.

First import the data as Text,(rather than double-clicking). Then in B1 or some other column enter:

=DATE(MID(A1,8,4),MID(A1,2,2),MID(A1,5,2))+TIMEVALUE(MID(A1,13,8))

and apply the desired formats:

enter image description here

Upvotes: 1

hughg
hughg

Reputation: 201

This problem is to do with how Excel is interpreting the date format (M/D/Y vs. D/M/Y). It appears your system is setup for D/M/Y date formats. You won't find the setting in Excel because it derives it from your system settings. Go to the Language settings and add US English. Then set it to default and try opening the CSV file again. Alternatively you can paste the csv file contents into a new sheet and use the 'Text-to-Columns' feature, which will let you specify the date format.

Upvotes: 2

Related Questions