excel - rounding time to the nearest 15 minutes

Question

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.

Answer 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