Excel: Using Time Values that Include Fractions of a Second

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

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0

Keywords :
Version :
Platform :
Issue type :