Working with milliseconds in Excel

A recent post at Eng-Tips looked for a solution to problems with working with times in Excel, when time differences are required to the nearest millisecond.

The problem is that although times may be entered as a time/date value, and formatted to display milliseconds, the format bar displays the time to the nearest second, and if you press F2 to edit a value, the time value is actually rounded to the second.  Pressing F2 then enter not only changes the displayed value, it changes the actual number:

The simplest way to avoid the problem is to enter the number as text, by starting with ‘ .  Pressing F2 then Enter will not change the text, but simple arithmetic will work:

Using text does have a few disadvantages though:

  • The time is rounded to the nearest millisecond, so if greater precision is required, this is not a suitable solution.
  • It is not possible to include days in the text, although an hours value greater than 24 is interpreted correctly.
  • The text may be misinterpreted if it is not entered in full.  For instance ‘8:23 is interpreted as 8 hours 23 minutes, but ‘8:23.0 is interpreted as 8 minutes 23 seconds.

An alternative approach is to convert the time to a date value in a formula, which will not be rounded by Excel.  A simple user defined function (UDF) that will do the calculation is shown below:

Function msTime(Secs As Double, Optional Mins As Double, _ 
Optional Hours As Double, Optional days As Double) As Double
Const SecsinDay As Long = 86400, MinsinDay As Long = 1440, HoursinDay As Long = 24

    msTime = days + Hours / HoursinDay + Mins / MinsinDay + Secs / SecsinDay

End Function

Examples of usage of the UDF are shown in the screenshot below.  Note that:

  • Differences of less than 1 millisecond are retained
  • Days may be included if required
  • All the arguments other than seconds are optional
  • The values may be entered as cell references, or directly as values, as for a built-in Excel function

A spreadsheet including the examples shown above, and all three lines of VBA code, may be downloaded from msTime.xlsb

Advertisements
This entry was posted in Computing - general, Excel, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

One Response to Working with milliseconds in Excel

  1. Pingback: Excel Roundup 20170601 - Contextures Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s