Converting MM:SS.ms to seconds using MS excel

excel time string-formatting data-conversion seconds

82,439

Solution 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:

enter image description here

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.

Solution 2

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.

82,439

Author by

Jambobond

Updated on March 15, 2020

Comments

Jambobond about 4 years
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!
Jambobond almost 12 years

This works great if the number is fully qualified. i.e. 0:00.11.111, but when the number is simply entered as 11:111 it gives the wrong answer.
Leniel Maccaferri almost 12 years

You're not entering it in the expected format. It should be: 11.111 and not 11:111.
barry houdini almost 12 years

Hello Leniel, re your edit at the end, surely if column is formatted as mm:ss.000 and you enter 02:11.111 you will still see exactly that, to see 131.111 you need a custom format of [s].000
Leniel Maccaferri almost 12 years

@barryhoudini this is not necessary here since Column C has a formula in place to do the conversion and display the correct values.
barry houdini almost 12 years

Yes, sorry, I missed the point......you could use that custom format in any case to display the current value differently, as with formula suggestion 11.111 would need to be entered as 0:11.111