Dienstag, 13. Oktober 2009

Excel ignores milliseconds

I often need to calculate in Excel time durations between two timestamps from log files that have the form like 14:23:45.342.
Now when entering such timestamps strings into Excel cells, they are not recognized as time, and calculating differences does give an error. When I use timestamps in the form like 14:23:45, ie. without the millisecond part, it works fine.
I use the custom time format "hh:mm:ss.000", but this seems not to work when there are milliseconds present.
While this bothered me for month now, i coincidentally stumbled over the solution: It seems that this behavior is caused by my regional settings, more exactly by the date separator that is set to '.'. As soon as i change this to something else it works.
My solution if i need to do time calculations with Excel: Before starting Excel, change the date separator in  Control Panel -> Regional and Language Options -> Customize -> Date to something else than '.'
If you know another solution, without the need to change the date separator, please fell free to leave a comment.

Update [April 2013]:
In the meantime W7/8 and Office 2010 is widley used and the above mentioned solution does not worky anymore (apart from being cumbersome to use) as the Date Separator is not easily available anymore.
But, I found another workaround:

I often import .csv files into excel. The timestamp in the files look like:
...;2013.03.13;12:24:59.378;Info...

Opening such a file in Excel shows:

  • The date has been correctly imported as "date" type (its right aligned)
  • The time has been imported as text (its left aligned)
Now create a new column and use the formula below to parse the time-text into a time value:
= A1 + TIME(MID(B1;1;2);MID(B1;4;2);MID(B1;7;2)) + VALUE(MID(B1;10;3))/(86400*1000)
Where:
  • A1 = cell containing the date
  • B1 = cell containing the time textCopy this formula to all new cells and format the new cells as time with milliseconds (eg: "TT. MMM JJ hh:mm:ss.000")
  • The new column is now a time type (its right aligned). It can be used for example to calculate time difference or to produce a scatter plot.

Keine Kommentare:

Kommentar veröffentlichen