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

Question

我有一个公式，可以计算几次 TIMES 的差值，并将其乘以 24 以获得总计的十进制值:

D10=(C9-D9)*24

我想像这样进行特殊舍入:

如果D9 为下午 1 点且C9 为下午 2:08，我希望 D10 为1.25

另外一句话

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

有人知道如何进行这种特殊的舍入吗？

正如道格所说:

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.

Answer 1

以下是根据您的需要划分差异的公式:

=(TRUNC((D9+VALUE("00:07"))*96)-TRUNC((C9+VALUE("00:07"))*96))*VALUE("00:15")*24