Microsecond time precision required

  • Thread starter Thread starter kenr
  • Start date Start date
K

kenr

Hi Guys

I am using Excel to parse a data file containing the time in
microseconds. I have tried a number of ways but cannot get around
Excel's millisecond precision limit.
For example if the inputted file contains 15:34:59.178533, I would like
to be left with just 0.178533. However excel always rounds the value
up to 15:34:59.179. :confused:

I've tried custom formats etc and am very frustrated at what I thought
would be a simple task.

I've used the HOUR(),MINUTE(), and SECOND() functions to be left with
the sub-second part but can only get 0.179.

I'm sure someone must have faced this problem before and I have
searched the archives but can't find anything. I would REALLY
appreciate some help on this please .

=8-)
 
kenr said:
Hi Guys

I am using Excel to parse a data file containing the time in
microseconds. I have tried a number of ways but cannot get around
Excel's millisecond precision limit.
For example if the inputted file contains 15:34:59.178533, I would like
to be left with just 0.178533. However excel always rounds the value
up to 15:34:59.179. :confused:

I've tried custom formats etc and am very frustrated at what I thought
would be a simple task.

I've used the HOUR(),MINUTE(), and SECOND() functions to be left with
the sub-second part but can only get 0.179.

I'm sure someone must have faced this problem before and I have
searched the archives but can't find anything. I would REALLY
appreciate some help on this please .
Click to expand...

maybe the problem is limited digits number, because excel keeps
dates & times in the same double-precision number.
maybe second's decimals suffers?
is your input data file simple text file, like "csv" or something.
maybe to read-in as pure text, and extract last part of digits with string
manipulating
functions?
 
It appears to me that this is a problem with the parser. If I read in
your data from a text file and let the parser interpret it:

15:34:59.178533

the result is rounded to the millisecond:

0.649296053240741

OTOH, if I read in your data as text and use a formula to convert it:

B1: =LEFT(A1,8)+MID(A1,9,255)/86400

the result is accurate to the microsecond:

0.649296047835648
 
Ken,

Given the following: Excel stores date/time values in units of days,
starting at Jan 1, 1900. Time is simply fractional days (8 hours (8:00 AM)
would be .3333333333). Excel stores these, as it does all numbers, as
floating-point numbers with a precision of approximately 15 decimal digits.
Thus a date/time of Jan 0, 1900 at 8:00 AM would be 0.333333333333333, more
than enough precision for microsecond resolution. But a date/time like Jan
1, 9999 8:00:00 AM would be a value of 2958101.33333333, barely enough for
millisecond precision.

If you're reading a text file, Excel will convert your time text strings to
its date/time values, unless you cause the Text Import Wizard to be invoked,
which will let you format the field for text. But then Excel's normal
date/time functionality is not available. So the question is, what do you
need to do with these microsecond-precision time values?

More on Excel's date/time methodology at www.cpearson.com. More on Excel
and text files at http://www.smokeylake.com/excel/textfiles.htm
 
Back
Top