17

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!

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
Jambobond
  • 619
  • 3
  • 12
  • 23

2 Answers2

19

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.

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
  • 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. – Jambobond Jul 10 '12 at 16:10
  • You're not entering it in the expected format. It should be: `11.111` and not `11:111`. – Leniel Maccaferri Jul 10 '12 at 17:17
  • 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` – barry houdini Jul 10 '12 at 18:01
  • @barryhoudini this is not necessary here since Column `C` has a formula in place to do the conversion and display the correct values. – Leniel Maccaferri Jul 10 '12 at 18:03
  • 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 – barry houdini Jul 10 '12 at 18:13
3

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.

Marcin Nabiałek
  • 109,655
  • 42
  • 258
  • 291
Guest101
  • 31
  • 1