使用 VBA 将十进制数转换为分钟和秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10421849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert decimal number to minutes and seconds using VBA
提问by user1371038
I have time represented by a decimal number that I need to convert to minutes and seconds in Excel using VBA.
我有一个十进制数表示的时间，我需要使用 VBA 在 Excel 中将其转换为分钟和秒。
Example: The number before the decimal point gives the minutes as 0.197683577 (0 minutes) and the number multiplied by 60 gives the seconds = 0.197683577*60 = 11.86101462 (12 seconds) = 0:12
示例：小数点前的数字给出分钟为 0.197683577（0 分钟），数字乘以 60 给出秒 = 0.197683577*60 = 11.86101462（12 秒）= 0:12
I know I could do this by copying and pasting into text format and separating the number to the left and right using text to columns of the decimal point and then add ":" in between but there must be a way to calculate this.
我知道我可以通过复制并粘贴到文本格式并使用文本到小数点的列将数字分隔到左边和右边来做到这一点，然后在它们之间添加“：”，但必须有一种计算方法。
采纳答案by user1371038
The VBA function didn't work for me but I found a workaround within Excel:
VBA 函数对我不起作用，但我在 Excel 中找到了一种解决方法：
=TRUNC(W11/60)&":"&IF(ROUND(((W11/60-TRUNC(W11/60))*60),0)=0,"00",ROUND(((W11/60-TRUNC(W11/60))*60),0))`
Where W11is time in the form of a decimal number like 900.3201306.
The result is in minutes 15:00
W11以十进制数形式表示的时间在哪里900.3201306？结果在几分钟内15:00
Basically, you take the truncated decimal minutes minus the decimal minutes+seconds to give you seconds only. Then you take the decimal seconds * 60to give you seconds+milliseconds. Round this to give you seconds. If seconds are 0 then write "00" to get the correct format. Then add the truncated minutes plus the seconds.
基本上，您将截断的十进制分钟减去十进制分钟+秒只给您秒。然后你拿的decimal seconds * 60给你seconds+milliseconds。围绕这个给你几秒钟。如果秒为 0，则写入“00”以获得正确的格式。然后添加截断的分钟加上秒。
This formula seems to be working.
这个公式似乎有效。
回答by GSerg
Public Function FromDecimalTime(ByVal t As Double) As Date
FromDecimalTime = TimeSerial(0, Fix(t), (t - Fix(t)) * 60)
End Function
Same with a formula:
同一个公式：
=TIME(0,TRUNC(A1),(A1-TRUNC(A1))*60)
Well, not the same actually. TimeSerialwill round number of seconds according to rounding rules (11.86-> 12), and TIMEwill truncate (11.86-> 11). But you can apply a rounding function to its last argument.
嗯，实际上不一样。TimeSerial将根据舍入规则 ( 11.86-> 12)舍入秒数，TIME并将截断 ( 11.86-> 11)。但是您可以将舍入函数应用于其最后一个参数。
