excel -四舍五入时间到最近的15分钟

I have a formula that computes the difference of several TIMES and multiplies it by 24 to get a decimal value for the total:

我有一个公式，它计算了数倍的差值然后乘以24得到一个小数的总数:

D10=(C9-D9)*24

I would like to do special rounding like this:

我想做如下特殊的圆:

if D9 is 1pm and C9 is 2:08 pm, i would like D10 to be 1.25

如果D9是1pm, C9是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.

4 个解决方案

#1

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

#2

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)

#3

An Excel Function Version:

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 *轮(15分钟(A1)/ 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

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)

地板(n / d = 0.25)

remainder=n mod 0.25

剩余0.25 = n国防部

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键“tick”到十进制函数(又是什么?)这可能是更好的选择。

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)

#4

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)