Converting Time to a decimal format

[Michael and Kathryn Clarke]

Can anyone provide a method for converting a race time eg 0:09:05.0 into a decimal format that looks like 9.05

Unfortunately the template I am trying to auto-populate requires this rather odd format.

Challenges I cannot get past so far:

- Time defaults to a 'time of day' regardless of how i format the cell so in the formula bar is it 12:09:05 am

- If I try a REPLACE or LEFT/RIGHT text style approach it views it as a decimal record, assuming a portion of the 24hr day ie 0.001400463

So far with a mixture of calculations I can get all double digit times ie 11:00 to work.

But cannot get the single digit minutes ie 07:45 to work, or those with a seconds result below 10, ie 12:02.

Method so far:

0:02:11

2.18

2.

.

11

2..11

Note double decimal in right most field.

or

0:12:05

12.08

12

.

5

12.5

Note missing 0 for the :05

It works perfectly for examples with 2 digit minutes and anything over 10 for seconds.

0:12:25

12.41

12

.

25

12.25

Using formulas: (note starting time is in C5

=ROUNDDOWN(SUM(C5*60*24),2)

=LEFT(D5,2)

.

=ROUNDUP(SUM(D5-E5)*60,0)

=CONCATENATE(E5,F5,G5)

I'm sure there is an easier way..... but I cannot see it so far

[#2]

Hi,

Not sure i understood everything, but did you try to apply custom format "mm.ss" to the cell (use Ctrl+1)?