To display tenths, hundredths, or thousandths of a second in Excel, users need to build a custom time format.
This capability is essential for tasks requiring high precision, such as tracking athletic performance, timing scientific experiments, or recording reaction speeds. Customizing the time format allows you to show fractions of a second, helping you capture the exact level of detail needed for accurate analysis.
For example, the image below shows the 100m men’s final results from the Paris Olympic Games. Column D uses Excel’s standard time format, losing precision from Column C by rounding to full seconds and hiding tenths and hundredths.
Displaying Tenths, Hundredths, or Thousandths
To add the fractional seconds, you must create your own number format.
- Select your cells
- Open the Custom Number Format with Ctrl + 1 or by selecting the last option in the Number Format DropDown list
- Then, in the Dialog Box, write in the Type field one of the following code
- Tenths of a Second: mm:ss.0
- Hundredths of a Second: mm:ss.00
- Thousandths of a Second: mm:ss.000
With this custom number format, now you display the thousandth in the Excel cells.
When Fractional Seconds Matter
- Sports Timing: In races or competitive sports, recording results to the nearest hundredth or thousandth ensures accuracy in rankings.
- Scientific Experiments: Time intervals in milliseconds often affect experimental outcomes, especially in physics and biology.
- Reaction Times: When measuring response times in psychology studies, capturing to the thousandth helps provide accurate averages and insights.
Displaying tenths, hundredths, or thousandths of a second in Excel is a straightforward way to ensure data precision, making this feature invaluable for professionals who rely on exact timing.
27/03/2019 @ 02:48
how do I convert this text string “2019-03-24 15:00:28 759” into a date/time fromat in excel???