Explanation
In the worksheet shown, we have race results for an 800m race.
The goal is to display time in minutes, seconds, and hundredths of a second (centiseconds).
Dealing with times that include fractional seconds can be tricky in Excel.
However, several quirks in Excel make working with fractional seconds more difficult than you would expect.
This article explains these quirks and provides tips and formulas to make the process easier.
Note when you add a placeholder for hours, you must enter time starting with hours.
In short, you must enter time according to specified placeholders.
For example, enter “01:45.73” for “1 minute, 45.73 seconds”.
Often, it is easiest to enter the complete time again.
This is a puzzling problem.
The first step is to multiply by 1440.
Excel stores dates and times as serial numbers where one day is 1.0.
MOD is a shortcut here.
Multiplying 0.847 x 60 results in 50.82 seconds:
This matches the value for seconds seen in cell C5.
TIME simply removes the decimal part (0.5) during calculation.
Likewise, if we make a run at provide 30.5 forseconds, the decimal portion is discarded.
The TIME function is useful when you want to assemble a proper time inside another formula.
TIMEVALUE Function
The Excel TIMEVALUE function converts a time represented as text into a proper Excel time.
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
