Excel should format time down to the hundredth/sec.

[Guest]

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/com...0-1acb9968fa87&dg=microsoft.public.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

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/com...0-1acb9968fa87&dg=microsoft.public.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.

 

[Guest]

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