Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Dealing with Small Time Values.

# Dealing with Small Time Values

by Allen Wyatt
(last updated November 6, 2018)

Zuzana has a need to perform calculations with very small time increments, such as thousandths of a second. She wonders how small of times Excel can deal with and how she can format for the display of the small increments.

Because of the way in which Excel stores times internally, it can theoretically deal with a time increment much smaller than a thousandth of a second. I say theoretically because there are a lot of factors that can negatively impact that precision.

As an example, consider that times and dates are stored in Excel such that full days are stored to the left of the decimal point and fractions of days are stored to the right. If you wanted to store a value as small as a hundred billionth of a second, you could conceivably do it. To store such a number in this format you end up with 1 day divided by 24 hours divided by 60 minutes divided by 60 seconds divided by 100,000,000,000. This results in a number that looks like this:

0.000000000000000115740740740741

Notice that there are a bunch of zeros followed by 15 significant digits. This is because Excel can only store a number that includes up to 15 significant digits. If you start doing anything else with this value, you ruin the value. For instance, you could add 3 to the value, signifying the addition of three days. Excel would then render this as the result:

3.000000000000000000000000000000

Notice that the small time increment disappeared. This happened because in the result of the addition, everything after the 3 became "significant," and Excel can only keep track of up to 15 significant digits, which was all zeros in the original number.

A thousandth of a second is a much longer duration than a hundred billionth of a second; it is represented in Excel's internal formatting as this:

0.0000000115740740740741

If you add three days to this value, you end up with this:

3.00000001157407

This is still accurate enough to render thousandths of a second just fine.

Of course, it doesn't do much good to store thousandths of a second internally if you can't display them in Excel. This is where custom formatting comes into play. You can create a custom format (as described in other issues of ExcelTips) that will display thousandths of a second just fine. Here's the format to use:

h:mm:s.000

If you try to use more zeros in the format (to display smaller increments of time), Excel will balk. The smallest increment you can display using custom formats is a thousandth of a second.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9198) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Dealing with Small Time Values.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Searching for Borders

Want to find all the paragraphs in your document that have borders applied to them? The regular Find and Replace tool ...

Discover More

Freezing Cell Size when Inserting Pictures

Insert a picture into a table cell, and you may quickly find that the table is no longer the size you expected. Here's ...

Discover More

Pictures inside Shapes

Filling one of Excel's drawing shapes with a picture of your choosing is a neat trick. Here's how to do it.

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

Entering or Importing Times without Colons

Enter a time into a cell and you normally include a colon between the hours and minutes. If you want to skip that pesky ...

Discover More

Rounding Time

Need to round the time in a cell to a certain value? There are a couple of ways you can do this with a formula.

Discover More

Rounding to the Nearest Quarter Hour

When entering times in a worksheet, you may have a need to round whatever you enter to the nearest 15-minute increment. ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 4 + 1?

2020-07-18 13:42:46

John Mann

The orignal question, as quoted above, lacks context. Does it refer to time of day using small increments, or time intervals?

There can be differing under4standings of "time". The tip above deals with time as meaing "time of day". "Time" can also meant "time interval" as a number which is indepencant of the "time of day". Back in the days when I was an electronic technician dealing with digital circuits and components, we often had speciications for thisng like "probagation delay" or the time it takes for the output of a device to respond to one of the inputs (eg the Trigger input or Reset input of a flip-flop or pehaps a counter). When putting together a circuit it was often necessary to add these various time together to ensure that we didn't get possible errors caused by the delays through one part of a circuit compared to another. In that context a millisecond (thousandth of a second) was a very long time - we used microseconds and nanosecons (millionth and thousandth of millioenth of a second).

In this type of context, formating times in a spreadsheet wouod be simply as numbers, not as times (time of day)

##### This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.