Formatting for Hundredths of Seconds

Summary: When you display a time in a cell, Excel normally displays just the hours, minutes, and seconds. If you want to display the time with more precision, you'll need to create a custom format, as described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Paolo wants to record times in an Excel workbook, but he needs the times to be precise, including hundredths of a second. What he would like is a format such as 1.41.73, meaning 1 minute, 41 seconds, and 73 hundredths.

This type of formatting can be easily applied to a cell in the following manner:

Select the cell or cells you want to format.
Display the Format Cells dialog box. (In Excel 2007 display the Home tab of the ribbon and click the small icon at the lower-right corner of the Number group. In older versions of Excel choose Cells from the Format menu.)
Make sure the Number tab is selected. (Click here to see a related figure.)
In the Category list, choose Custom.
In the Type box, enter the following: [h]:mm:ss.00
Click OK.

The format shows elapsed time, with hours, minutes, seconds, and hundredths of seconds. If you prefer, you can change what delimiters are used between each element of the time. For instance, if you want to use decimal points and drop off the hours, you can use the following format in step 4: mm.ss.00.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3085) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Your Data, Your Way! Want the greatest control possible over how your data appears on the page? Excel's custom formats can provide that control, and ExcelTips: Custom Formats can unlock the secrets to creating your own custom formats.

Check out ExcelTips: Custom Formats today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Formatting for Hundredths of Seconds