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