How can I display hundreths of seconds?

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

Hi!

Format the cells as m:ss.000

Biff

"Diamond Jones" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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.


Diamond Jones <[email protected]> wrote:
> 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 L. Smith
Columbus, Ohio

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

"Gary Smith" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> Diamond Jones <[email protected]> wrote:
>> 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 L. Smith
> Columbus, Ohio

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




Diamond Jones wrote:
>
> 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
>
> "Gary Smith" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> > Diamond Jones <[email protected]> wrote:
> >> 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 L. Smith
> > Columbus, Ohio

--

Dave Peterson