2

I have a CSV file that contains strings meant to be interpreted as dates with minutes, seconds and miliseconds.

In particular entries look like this:

"07/31/2013 23:59:32.864",otherdata,moredata
"08/01/2013 00:00:02.863",otherdata,moredata

For 7/31/2013 Excel decided to use the "General" format and displays the value as

07/31/2013 23:59:32.864

This is as expected.

However, for 08/01/2013, Excel goes for a "Custom" format of "mm:ss.0" and displays the value as

00:02.9

This doesn't help. If I change the cell format to "General" the value becomes 41282.00003.

Oddly enough, if I change the August into a July (like 07/01/2013), the value displayed is now

07/01/2013  00:00:03

with a "Custom" format of "dd/mm/yyyy hh:mm".

How can I stop Excel from choosing various formats based on what seems to me random reasons and agree to one format to display these dates?

Note that this has little to do with Excel confusing days for months. A date 08/13/2013 (where 8 is clearly the month) loses its miliseconds and is displayed as

08/13/2013  23:01:06

with a "Custom" format of "mm:ss.0".

It's completely weird.

2 Answers 2

6

You need to straighten out your regional settings and your date formats and align them with the month and day order of the dates in the CSV. They don't seem to gel at the moment.

41282.00003 is 8 January 2013 and not 1 August 2013.

Excel will try to interpret a date according to your computer's regional settings. If the regional settings are DMY and the date to be interpreted is 07/31/2013, the DMY order will not work and Excel will interpret the data as text. That is what you see. Text that looks like a date/time value. Try to format that apparent date/time value differently. You will see that you cannot, because it is text.

But if the next row of data has 08/01/2013, this fits very well into the regional setting's DMY scheme and it will be returned as 8-Jan-2013. You can change the cell's format to custom format

dd/mm/yyyy hh:mm:ss.000

and it will show as 08/01/2013 00:00:02.863

enter image description here

The value in cell A1 is the text value, not a real date time. The cell format is "General" and no amount of number formatting will change its appearance.

The value in cell A2 is a real date/time value, formatted with the above mentioned custom format.

When you import dates, take extra double care to check the order of day and month in the imported data. When you use the import wizard, you can specify what order the source data is in and all dates will be imported consistently.

Let's take a closer look at the second (real) date. 08/01/2013 00:00:02.863 formatted with "General" displays as 41282.00003 and formatted with a proper date shows as 8-Jan-2013. Fine.

If you increase the decimals for the General format, you will find that the actual underlying number is 41282.0000331366. This number has 15 numeric digits.

Formatted as a date, you can edit it and change the day from 8 to 7. The result will show in "General" Format as 41281.00003, but if you increase the number of displayed digits, you will see that the number is 41281.00003472220

Huh?

How come? We only subtracted one day, so only the number before the decimal point should change.

Well, Excel has a built-in accuracy of only 15 digits for any number. Numbers with more digits will be rounded or the last digits will be replaced with zeros. Also, there is a well-known bug in Excel that affects the accuracy of numbers where the 15 digit limit is reached.

I think this is one example where the bug rears its ugly head.

When the date portion of our date/time value is changed, it will also cause a re-assessment of the decimals, which will lead to some rounding and inconsistent behaviour after the 4th decimal. Therefore, the actual second and millisecond data will be off.

See if this screenshot helps clarify:

enter image description here

The values in columns B to D all reference column A. The only difference between A1 and A2 is a manual change of the date from 08/01 to 07/01 (where the 01 is January, according to the regional settings of DMY).

The "General" format shows both values with a x.0003 decimal value. Extending the decimals shows that there is quite a difference in the decimals following the 4th decimal.

Since the desired end result is a value that shows seconds and milliseconds, the decimals after the 4th decimal really make a difference, and when the value is formatted with a custom format that shows seconds and milliseconds, that difference shows (in column A).

Also, note the three cells with the numbers consisting of just 15, 16 and 17 digits of 1, and how Excel simply replaces any digit after the 15th with a zero, because it cannot display a higher accuracy.

6
  • Excel just opens the file without showing any kind of import wizard. The locale is Switzerland/German but I do wonder why Excel interprets 08/01 as January but not 07/01. Aug 7, 2013 at 11:07
  • Oh, but 07/01 is 7-Jan, not 1-Jul!!! I've added some more explanation to the answer.
    – teylyn
    Aug 7, 2013 at 11:44
  • Also, instead of opening the CSV file use Data > From Text > (select CSV file). This will show the import wizard. From tests with your data sample, I see that the dates don't come in nicely, since they are enclosed in quotes. You can first use a fixed width separation and single out the "|date|time|"|therestofthedata. Make sure that when stepping through the wizard you select the "date" column and specify the order of MDY. (continued ...)
    – teylyn
    Aug 7, 2013 at 12:01
  • (... continued): Now date and time are in two columns. Insert a column and use a formula to add the date and time values, apply your desired format. Copy and paste as values over the formula, then delete the original two columns. In a next step, select the column with the remaining data that has not yet been spliced and use Data > Text to columns to get the rest of the data into their own columns with the comma delimiter.
    – teylyn
    Aug 7, 2013 at 12:02
  • "but 07/01 is 7-Jan, not 1-Jul!!!" Never doubted it. Was wondering why Excel saw it differently. It was 08/01 as January, but not 07/01. Aug 7, 2013 at 13:31
-2

Very Easy solution:

  1. Select all date column.
  2. Select General Button.
  3. Sort number and number stored as text sparately.

    enter image description here

  4. You will find data in number and date form as:

    enter image description here

  5. convert date format into text to column with delimited option and in other write / - etc as shown on your date:

    enter image description here

    enter image description here

  6. Apply Date formula as:

    enter image description here

  7. Then do paste special of date (copy write click paste special).

Your problem is solved.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .