I am looking for a neat way of converting a cell from
我正在寻找一种简单的方法来转换一个单元格
Minutes:Seconds.Milliseconds to
分:秒。毫秒
Seconds.Milliseconds
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
我现在有一些东西已经准备好了，但是这有点陈腐，我相信一定有一些很好的excel特性可以帮助我完成:P
Thanks!
谢谢!
2 个解决方案
#1
17
Do this:
这样做:
Place values 0:0:11.111 and 0:1:11.111 in cells B3 and B4 respectively.
将B3和B4细胞的值分别为0:0:11.111和0:11.111。
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:
现在把它格式化为毫秒……选择B3和B4，右击并选择格式单元格。在自定义中，在标记为Type的文本框中输入以下内容:
[h]:mm:ss.000
Now on cell C3 put the following formula:
现在在C3细胞上放以下公式:
=B3*86400
Fill C4 with the same formula...
用同样的公式填满C4…
Format column C as Number with 3 decimal places.
将列C格式化为小数点后三位的数字。
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.
如果你想只输入MM:SS。ms . you可以使用自定义格式格式化整个B列，如:mm:ss.000。现在你可以输入值为02:11。111它会相应地转换为131.110。希望它可以帮助。
#2
3
say your time is in cell A1, place this formula in B1
假设你的时间在细胞A1中，把这个公式放在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).
如果时间小于一分钟，它输出的时间不变，大于1分钟，它将其转换为秒和毫秒(2位小数)。
This will only work if your time in A1 is 10 seconds or greater.
这只在你在A1中的时间是10秒或更多的时候有效。
#1
17
Do this:
这样做:
Place values 0:0:11.111 and 0:1:11.111 in cells B3 and B4 respectively.
将B3和B4细胞的值分别为0:0:11.111和0:11.111。
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:
现在把它格式化为毫秒……选择B3和B4，右击并选择格式单元格。在自定义中，在标记为Type的文本框中输入以下内容:
[h]:mm:ss.000
Now on cell C3 put the following formula:
现在在C3细胞上放以下公式:
=B3*86400
Fill C4 with the same formula...
用同样的公式填满C4…
Format column C as Number with 3 decimal places.
将列C格式化为小数点后三位的数字。
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.
如果你想只输入MM:SS。ms . you可以使用自定义格式格式化整个B列，如:mm:ss.000。现在你可以输入值为02:11。111它会相应地转换为131.110。希望它可以帮助。
#2
3
say your time is in cell A1, place this formula in B1
假设你的时间在细胞A1中，把这个公式放在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).
如果时间小于一分钟，它输出的时间不变，大于1分钟，它将其转换为秒和毫秒(2位小数)。
This will only work if your time in A1 is 10 seconds or greater.
这只在你在A1中的时间是10秒或更多的时候有效。