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

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.