Calculating time in Excel

[Hartvig]

Hi,

How do I calculate times in Excel with the following syntax:

hh.mm.ss.sss - hh.mm.ss.sss

e.g

01.59.59.999 minus 01.56.45.345

Please observe that the 3 last digits are 1/100 of a secund so th
formula need to take this in to account when calculating correc
results.

I need this for calculating racetime results in Hill Climbs ;-)

Thanks in advance !

Henrik - Denmark
www.racetiming.d

 

[JE McGimpsey]

One way:

If your values are already entered as times:

A1: 01.59.59.999
A2: 01.56.45.345

A3: =A1-A2 ===> 00.03.14.654

when all are formatted with

Format/Cells/Number/Custom hh.mm.ss.000

If instead your values are text, one way:

A3: =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(C1,".",":",2),".",":",1)) -
TIMEVALUE(SUBSTITUTE(SUBSTITUTE(C2,".",":",2),".",":",1))

again, formatted as hh.mm.ss.000

 

[Hartvig]

Thanks,

Since yesterday I found out that the syntax was wrong - the correct
syntax in the Danish version of Excel is as follows:

hh:mm:ss,000 when I format cells using this formula there is no problem
calculating times with 1/100 secund.

But now I got a new problem !

How do I sort results with the lowest time on top ???

Yours Sincerely

Henrik - Denmark