excel for handeling results in a swimming competition

[Gitte]

If I format the cell mm:ss,000 it still shows date and year plus 00:00:000.
I need to type e.g. 258940 and have it shown as 2:58:940.
Afterwards I would like if excel could do a calculation to show the
difference between the result from the last competition and the present
competition - e.g.

first column second column third column
(typed) (typed) (calc.)
2:58:940 2:58:800 -0:00:140

Can anywne help? Thanks

 

[Frank Kabel]

Hi
you have to enter your time with the colons together. Without entering
colons you'll need VBA to convert your number. So try enterung
25:89,940

 

[Gitte]

Hi again
Thank you for your quick response

When I enter 2:58,460 it is shown as 2:58;,460. But never mind - it works.
However - is there any easy way to avoid having to typr the : and , ??
It would be much quicker if I only would have to type 258460 ?

"Frank Kabel" skrev:

 

[Frank Kabel]

Hi
not without VBA (using an event procedure). See:
http://www.cpearson.com/excel/DateTimeEntry.htm

 

[Max]

It would be much quicker if I only would have to type 258460 ?

Maybe try this:

Assume timings will be entered as per above
in cols A and B, row1, down

Put in C1:

=(LEFT(TEXT(A1,"0000000"),2)&":"&MID(TEXT(A1,"0000000"),3,2)&"."&RIGHT(TEXT(
A1,"0000000"),3))+0

Format C1 as custom: mm:ss.000

Copy C1 across to D1, fill down

Cols C and D would give the time "conversion"
in the desired format (e.g.: 02:58.460, )
for the numeric inputs in col A and B

To "show" (as text) the timing differences
between cols C and D in col E,

Try in E1:

=IF(D1<C1,"-"&TEXT((C1-D1),"mm:ss.000"),"+"&TEXT((D1-C1),"mm:ss.000"))

Copy E1 down

Note: Think you'd need to change the period "."
in the formula for C1, the part: .. &"."&RIGHT...
to suit the language (use comma instead?): ","
and in the formula for E1 as well: "mm:ss.000"