使用MS excel将MM:SS.ms转换为秒 [英] Converting MM:SS.ms to seconds using MS excel
问题描述
我正在寻找一种从
Minutes:Seconds.Milliseconds
转换为
Seconds.Milliseconds
ie
11.111 = 11.111
1:11.111 = 71.111
我现在有一些东西在,但它有点黑客,我相信必须有一些很好的excel功能来为我做这件事:P
谢谢!
执行此操作:
单元格<$ c中的位置值 0:0:11.111
和 0:1:11.111
$ c> B3 和 B4
现在格式化为毫秒...选择单元格 B3
和 B4
,右键单击并选择格式化单元格。在自定义中,将以下内容放在标有的文本框中,键入
:
[h]:mm:ss.000
现在在单元格 C3
输入以下公式:
= B3 * 86400
/ pre>
填充
C4
具有相同的公式...
格式栏
C
asNumber
,带3位小数。
你完成了! :)
以下是我所做的尝试和工作的截图:
编辑: p>
您只需输入
MM:SS.ms
,您可以格式化整个B
列具有自定义格式,如:
mm:ss.000
。现在,您可以输入02:11.111
的值,它会相应地转换,让您131.110
。希望有帮助。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!
解决方案Do this:
Place values
0:0:11.111
and0:1:11.111
in cellsB3
andB4
respectively.Now format it to account for the milliseconds... Select cells
B3
andB4
, right click and choose Format Cells. In Custom, put the following in the text box labeledType
:[h]:mm:ss.000
Now on cell
C3
put the following formula:=B3*86400
Fill
C4
with the same formula...Format column
C
asNumber
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 entireB
column with a custom format like:mm:ss.000
. Now you can enter values as02:11.111
and it'll convert it accordingly giving you131.110
. Hope it helps.这篇关于使用MS excel将MM:SS.ms转换为秒的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!