excel - rounding time to the nearest 15 minutes

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

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

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.

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

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)

An Excel Function Version:

As per request in comments, here's an excel function only version:

ROUND UP TO NEXT 15 MINUTE BOUNDARY:

=TIME(HOUR(A1),15*CEILING(MINUTE(A1)/15,1),0)

• N.B. replace A1 with (t2-t1) as per the original question

ROUND TO NEAREST 15 MINUTE BOUNDARY:

=TIME(HOUR(A1),15*ROUND(MINUTE(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

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...

(do in vba if you need to reuse this a lot)

convert to fraction

d=floor(n / 0.25)

remainder=n mod 0.25

x=0.25 * round (remainder / 0.25)

answer=(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.

coded it up in vba... basic testing only:

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)

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)