Untitled

excel - rounding time to the nearest 15 minutes

D10=(C9-D9)*24

       

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.

       

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

       

=MROUND((C9-D9)*24,0.25)

       

=(ROUND(+D9*96,0)-ROUND(+C9*96,0))/4

       

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