Excel should format time down to the hundredth/sec.

[John Krick]

I would like to be able to format the cells within Excel so that if I typed
in my athlete's time from a race as 153268 or 3245, it would show up in the
cell as 15:32.68 or :32.45 respectively. I would also like to be able to
then add or subtract time in that same format. Right now I can only format
the cell to display those times as 15:32.6 or 00:00.0 (nothing at all). I do
race time analysis with excel but I have to convert the time into all seconds
if I want to add/subtract/divide or whatever. Can you add a format that
would recognize time not in the sense of how a clock on the wall is read but
how a stop watch is recognized?

I'm currently using the MIcrosoft Office Excel 2003 version.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

[Peo Sjoblom]

How would excel know how to do that compared if you meant to type in the
number 153268 since formatting never changes the underlying value
just the display, for people that find it too hard to type in colons there
are VBA solutions like in here

http://www.cpearson.com/excel/DateTimeEntry.htm


--

Regards,

Peo Sjoblom

"John Krick" <John [email protected]> wrote in message
news:[email protected]...
> I would like to be able to format the cells within Excel so that if I
typed
> in my athlete's time from a race as 153268 or 3245, it would show up in
the
> cell as 15:32.68 or :32.45 respectively. I would also like to be able to
> then add or subtract time in that same format. Right now I can only
format
> the cell to display those times as 15:32.6 or 00:00.0 (nothing at all). I
do
> race time analysis with excel but I have to convert the time into all
seconds
> if I want to add/subtract/divide or whatever. Can you add a format that
> would recognize time not in the sense of how a clock on the wall is read
but
> how a stop watch is recognized?
>
> I'm currently using the MIcrosoft Office Excel 2003 version.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
>
http://www.microsoft.com/office/comm...lic.excel.misc

[JE McGimpsey]

If you format your cell with Format/Cell/Number/Custom

mm:ss.00

you can display 15:32.68, and you can always use that value to add and
subtract. It won't necessarily show up that way in the formula bar, but
it will in the cell.



In article <[email protected]>,
"John Krick" <John [email protected]> wrote:

> I would like to be able to format the cells within Excel so that if I typed
> in my athlete's time from a race as 153268 or 3245, it would show up in the
> cell as 15:32.68 or :32.45 respectively. I would also like to be able to
> then add or subtract time in that same format. Right now I can only format
> the cell to display those times as 15:32.6 or 00:00.0 (nothing at all). I do
> race time analysis with excel but I have to convert the time into all seconds
> if I want to add/subtract/divide or whatever. Can you add a format that
> would recognize time not in the sense of how a clock on the wall is read but
> how a stop watch is recognized?
>
> I'm currently using the MIcrosoft Office Excel 2003 version.

[Gary''s Student]

Colons help to define the fields and avoid confusion. You can set up custom
formatting that will allow you to work in thousandths of a sec:

hh:mm:ss.000 with more than necessary precision:
formatted general
01:00:00.000 0.041666666667
00:01:00.000 0.000694444444
00:00:01.000 0.000011574074
00:00:00.100 0.000001157407
00:00:00.010 0.000000115741
00:00:00.001 0.000000011574

--
Gary''s Student


"Peo Sjoblom" wrote:

> How would excel know how to do that compared if you meant to type in the
> number 153268 since formatting never changes the underlying value
> just the display, for people that find it too hard to type in colons there
> are VBA solutions like in here
>
> http://www.cpearson.com/excel/DateTimeEntry.htm
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "John Krick" <John [email protected]> wrote in message
> news:[email protected]...
> > I would like to be able to format the cells within Excel so that if I
> typed
> > in my athlete's time from a race as 153268 or 3245, it would show up in
> the
> > cell as 15:32.68 or :32.45 respectively. I would also like to be able to
> > then add or subtract time in that same format. Right now I can only
> format
> > the cell to display those times as 15:32.6 or 00:00.0 (nothing at all). I
> do
> > race time analysis with excel but I have to convert the time into all
> seconds
> > if I want to add/subtract/divide or whatever. Can you add a format that
> > would recognize time not in the sense of how a clock on the wall is read
> but
> > how a stop watch is recognized?
> >
> > I'm currently using the MIcrosoft Office Excel 2003 version.
> >
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the
> > suggestions with the most votes. To vote for this suggestion, click the "I
> > Agree" button in the message pane. If you do not see the button, follow
> this
> > link to open the suggestion in the Microsoft Web-based Newsreader and then
> > click "I Agree" in the message pane.
> >
> >
> http://www.microsoft.com/office/comm...lic.excel.misc
>
>
>