Limitations to # of Decimal places for seconds (time)?

  • Thread starter Thread starter Xprezons
  • Start date Start date
X

Xprezons

Hi All,

Maybe a dumb question.

I assume Excel imposes a limit to how many decimal places it ca
store for the second (time) value. Could anyone please tell me wha
this is?

I am loading time values in an Excel sheet as follows:

2004-09-23 09:35:21.821277

I need to specify a format for this to be displayed. The one tha
comes closest is > Custom dd/mm/yy hh:mm:ss.000

But it doesn't allow me to go up to 6 decimal places. Am I missin
anything? Or have I touched the limit?

I am also concerned about losing information accuracy. Will Excel roun
off and chuck out the last 3 decimal places? I wouldn't want to lose th
detail since I have to compare times subsequently and I need values upt
6 decimal places.

Thanks in Advance,
Xprezons
 
Xprezons,

Are you really recording every 1/10 millionth of a second? And, yes, Excel
limits you to three decimal places on the seconds

Anyway, the best solution would be to enter your decimal seconds in another
cell, and use that as the secondary sort/comparison basis.

Enter your date / time string as a string in cell A1, say, and use the
formula (formatted for 6 decimal places)

=VALUE(MID(A1,FIND(".",A1)+1,LEN(A1)))/1000000

to extract the decimal value of seconds.

The use this formula (formatted for date/time, down to seconds)

=VALUE(MID(A1,1,FIND(".",A1)-1))

to extract the date and time.

HTH,
Bernie
MS Excel MVP
 
Back
Top