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