vba excel - 将时间四舍五入到最接近的 15 分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6194343/
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
excel - rounding time to the nearest 15 minutes
提问by l--''''''---------''''''''''''
I have a formula that computes the difference of several TIMES and multiplies it by 24 to get a decimal value for the total:
我有一个公式可以计算几个 TIMES 的差值并将其乘以 24 以获得总数的十进制值：
D10=(C9-D9)*24
I would like to do special rounding like this:
我想做这样的特殊舍入：
if D9 is 1pmand C9 is 2:08 pm, i would like D10 to be 1.25
如果D9 is 1pm和C9 is 2:08 pm，我希望 D10 是1.25
another words
换句话
1:53 until 2:07 would be counted as 2
2:08 until 2:22 would be 2:15
2:23 through 2:37 would be 2:30
etc
Does anyone know how to do such special rounding?
有谁知道如何进行这种特殊的舍入？
as doug put it:
正如道格所说：
i need the accrued time from 1 PM to 2:08 PM should round up to 1.25 hours whereas 1 PM to 2 PM would round down to 1 hour, in other words to the nearest quarter hour.
采纳答案by Lance Roberts
Here is the formula that will partition out the difference as you wanted:
这是可以根据需要划分差异的公式：
=(TRUNC((D9+VALUE("00:07"))*96)-TRUNC((C9+VALUE("00:07"))*96))*VALUE("00:15")*24
回答by Brian Camire
If you want the difference in time in hours, but rounded to the nearest quarter hour, then maybe all you need is something like:
如果您想要以小时为单位的时间差异，但四舍五入到最接近的四分之一小时，那么您可能只需要以下内容：
=MROUND((C9-D9)*24,0.25)
回答by sgtz
An Excel Function Version:
Excel 函数版本：
As per request in comments, here's an excel function only version:
根据评论中的要求，这是一个仅限 excel 功能的版本：
ROUND UP TO NEXT 15 MINUTE BOUNDARY:
总结到下一个 15 分钟的边界：
=TIME(HOUR(A1),15*CEILING(MINUTE(A1)/15,1),0)
=时间(小时(A1),15*天花板(分钟(A1)/15,1),0)
- N.B. replace A1 with (t2-t1) as per the original question
- 注意按照原始问题将 A1 替换为 (t2-t1)
ROUND TO NEAREST 15 MINUTE BOUNDARY:
绕到最近的 15 分钟边界：
=TIME(HOUR(A1),15*ROUND(MINUTE(A1)/15,0),0)
=时间(小时(A1),15*轮(分钟(A1)/15,0),0)
btw: converting to string to do date manipulation should be avoided for both performance reasons, and probably also, geo reasons too. Perhaps netiher of these things are a concern for you.
顺便说一句：出于性能原因，可能还有地理原因，都应该避免转换为字符串来进行日期操作。也许这些事情都不会让你担心。
A VBA Version
VBA 版本
you can do it with div and mod functions. Not sure if you want it in vba or excel macros, but here's the approach. Don't have excel infront of me right now, so here goes...
你可以用 div 和 mod 函数来做到这一点。不确定您是否想要在 vba 或 excel 宏中使用它，但这是方法。现在没有excel在我面前，所以这里......
(do in vba if you need to reuse this a lot)
（如果您需要大量重复使用，请在 vba 中执行）
convert to fraction
转换为分数
d=floor(n / 0.25)
d=地板（n / 0.25）
remainder=n mod 0.25
余数=n mod 0.25
x=0.25 * round (remainder / 0.25)
x=0.25 * 圆（余数 / 0.25）
answer=(d * 0.25) + x
答案=(d * 0.25) + x
that should be close.
那应该很接近。
... just thought of the excel bond "tick" to decimal function (what was it again). This might be a better way to go.
...只是想到了excel债券“滴答”到十进制函数（又是什么）。这可能是一个更好的方法。
coded it up in vba... basic testing only:
在 vba 中编码...仅基本测试：
Public Function f(n As Double) As Double
Dim d As Double, r As Double, x As Double
d = Int(n / 0.25)
r = n - (d * 0.25) ' strange... couldn't use mod function with decimal?
x = 0.25 * Round(r / 0.25)
f = (d * 0.25) + x
End Function
USAGE:
用法：
(in a cell)
（在一个单元格中）
=f(c9-d9)
=f(c9-d9)
回答by chris neilsen
Here is another formual option
这是另一个正式选项
=(ROUND(+D9*96,0)-ROUND(+C9*96,0))/4
Converts each time serial to 1/4 hours (*96), rounds, adds results, and scales to hours (*4)
将每个时间序列转换为 1/4 小时 (*96)、四舍五入、添加结果并缩放到小时 (*4)
