Excel: Using Time Values that Include Fractions of a Second

 Last reviewed: November 30, 1994
Article ID: Q92559
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
  • Microsoft Excel for OS/2, versions 2.2, 3.0
  • Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

SUMMARY

In Microsoft Excel you can include fractions of a second in serial time values; however, if you type a time value that includes a decimal point, Microsoft Excel interprets the value as a text value rather than as a serial time value. For example, if you type "3:25:24", Microsoft Excel interprets this number as a serial time value, but if you type "3:25:24.5", Microsoft Excel interprets this value as text.

To convert this text value to a serial time value so you can use it in time calculations, see the formula in the example below (in the more information section of this article).

MORE INFORMATION

If you are working with time values that include fractions of a second, use the following formula to convert the text you enter to a serial time value that can be used in calculations.

In this example, type the time value in cell A1 (including the fraction of a second) and then type the entire formula in cell A2. Excel displays the serial value in cell A2.

A1: 3:25:24.5 A2: =TIMEVALUE(LEFT(A1,LEN(A1)-(LEN(A1)-FIND(".",A1)+1)))+VALUE(RIGHT( 

    A1,LEN(A1)-FIND(".",A1)))*(1/24/60/60/(10^(LEN(A1)-FIND(".",A1))))
Important: Type the entire formula in cell A2.

In the above formula, the TIMEVALUE() function is used to return the value while the FIND() and LEN() functions break the text string at the decimal point:

  • Everything to the left of the decimal point is a regular time serial number.
  • Everything to the right of the decimal point is some fraction of a second. Depending on the number of digits included after the decimal point, this could represent tenths, hundredths, or thousands (and so on...).

To allow the decimal point accuracy to vary, the function determines how many digits are included after the decimal point and then divides by 10 raised to that power. For example, if there are three digits included after the decimal point, the formula divides by 10^3 or 1000.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 218-220 "Microsoft Excel User's Guide," version 3.0 for Windows, pages 186-191

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.