Converting MM:SS.ms to seconds using MS excel

Question

I am looking for a neat way of converting a cell from

Minutes:Seconds.Milliseconds to

Seconds.Milliseconds

i.e.

11.111    = 11.111
1:11.111  = 71.111

I have something in place at the moment but its a bit hacky and I am sure there must be some nice excel feature to do this for me :P

Thanks!

Answer 1

say your time is in cell A1, place this formula in B1

=IF(LEN(A1)>5,VALUE(TEXT(A1,"[ss].00")),A1)

If the time is less than a minute it outputs the time unaltered, greater than 1 minute it converts it to seconds & milliseconds (2 decimal places).

This will only work if your time in A1 is 10 seconds or greater.

Answer 2

Do this:

Place values 0:0:11.111 and 0:1:11.111 in cells B3 and B4 respectively.

Now format it to account for the milliseconds... Select cells B3 and B4, right click and choose Format Cells. In Custom, put the following in the text box labeled Type:

[h]:mm:ss.000 

Now on cell C3 put the following formula:

=B3*86400

Fill C4 with the same formula...

Format column C as Number with 3 decimal places.

You're done! :)

Here's a screenshot of the attempt I made and that worked:

Edit:

As you wanna enter only MM:SS.ms you can format the entire B column with a custom format like: mm:ss.000. Now you can enter values as 02:11.111 and it'll convert it accordingly giving you 131.110. Hope it helps.