Display time difference - Formatting

Question

I have a table of times and a formula that works out the difference between the times but having some difficulties formatting the output.

For example I would like:

#    Time         Gap
1    23:55.798    
2    23:58.312    + 2.514
3    24:11.803    + 16.569
4    24:58.960    + 1:03.162

How do i get it to display like that as if I use + [m]:ss.000 it will display + 0:02.514 as the first difference but I would like it to display + 2.514 then show the 1:03.162 if it is a larger number.

Answer 1

In custom number formatting you can specify two formats with a cut off point, so you should be able to use this custom format

[>=0.000694444]"+ "m:ss.000;"+ "s.000

0.000694444 is the cut off - that's the decimal equivalent of 1 minute in Excel (1/1440) so anything at or over a minute is shown with format "+ "m:ss.000 and anything under is shown with format "+ "s.000