# How can I display hundreths of seconds?

1. ## 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

D

2. ## Re: How can I display hundreths of seconds?

Hi!

Format the cells as m:ss.000

Biff

"Diamond Jones" <kwanzaNOSPAM@optusnet.com.au> wrote in message
news:436ecb47\$0\$2117\$afc38c87@news.optusnet.com.au...
> 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
>
> D
>

3. ## Re: How can I display hundreths of seconds?

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 <kwanzaNOSPAM@optusnet.com.au> 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

> D

--
Gary L. Smith
Columbus, Ohio

4. ## Re: How can I display hundreths of seconds?

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" <bitbucket@example.com> wrote in message
news:eiaM5D14FHA.1416@TK2MSFTNGP09.phx.gbl...
> 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 <kwanzaNOSPAM@optusnet.com.au> 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

>
>> D

>
>
>
> --
> Gary L. Smith
> Columbus, Ohio

5. ## Re: How can I display hundreths of seconds?

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" <bitbucket@example.com> wrote in message
> news:eiaM5D14FHA.1416@TK2MSFTNGP09.phx.gbl...
> > 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 <kwanzaNOSPAM@optusnet.com.au> 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

> >
> >> D

> >
> >
> >
> > --
> > Gary L. Smith
> > Columbus, Ohio

--

Dave Peterson

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1