How can I display hundreths of seconds?

[Diamond Jones]

I'm trying to design a speadsheet to display lap times such as 1:26:329 (1
min, 26 secs etc) however I cannot sort the data in order from highest to
lowest. I've been inputing the data as 1.26239. This s OK to sort frm
Highest to Lowest but when you graph it there is a massiv gap beween 1.59960
(1.59.960) and 2.00765 (2.00.765) and there shouldn't be. I need Excel to
recognize hundreths of seconds

Please help
D

 

[Gary Smith]

The way you're entering the data, you don't have seconds or fractions of
seconds at all. You have minutes and (decimal) fractions of minutes.
1.59960, interpreted as minutes, is roughly a minte and 36 seconds.
2.00765 is roughly two minutes, hence the gap. You need to enter the
times so they are recognized as times: 1:26.329, for example. Note that
the first separator is a colon and the second a period (full stop).

In order to display thousandths (you said hundredths but the examples all
have thousandths) of seconds, select the relevant cells or columns, then
click Format > Cells > Number tab | Custom and enter "mm.ss.000"
(without the quotes) in the Type box.

 

[Biff]

Hi!

Format the cells as m:ss.000

Biff

 

[Diamond Jones]

Thanks so much (thanks to Biff as well). Elementary stuff huh!

Any way to convert 7.57.989 to 7:57.989 rather than retyping everything?

Thanks again
D

 

[Dave Peterson]

One way if you always had one of these formats: #.00.000 or ##.00.000

=LEFT(A1,FIND(".",A1)-1)/24/60
+MID(A1,FIND(".",A1)+1,2)/24/60/60
+RIGHT(A1,3)/24/60/60/1000

(all one cell)

formatted as mm:ss.000