Excel - round to the next 15 minutes

Question

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

D10=(C9-D9)*24 

I would like to do a special rounding as follows:

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

other 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 make such a 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 a formula that will share the difference as you like:

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

Answer 2

If you need a time difference in hours, but rounded to the nearest quarter, then maybe all you need is something like:

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

Answer 3

Excel Feature Version:

As requested in the comments, only the excel function version is available here:

ROUND TO THE NEXT BORDER OF 15 MINUTES:

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

• NB replace A1 with (t2-t1) according to the original question

ROUND TO THE NEAREST BORDER OF 15 MINUTES:

= TIME (HOUR (A1), 15 * ROUND (MINUTE (A1) / 15.0), 0)

btw: conversion to a string for date processing should be prevented, both for performance reasons and possibly also for geology reasons. Perhaps this is not so important to you.

---

VBA version

you can do this with div and mod functions. Not sure if you want it in vba or excel macros, but here is the approach. I don’t have right now, so here goes ...

(do in vba if you need to reuse it)

convert to fractions

d = gender (n / 0.25)

remainder = n mod 0.25

x = 0.25 * round (remainder / 0.25)

answer = (d * 0.25) + x

which should be close.

... just thought that the excel bond was ticking on a decimal function (which was again). This may be the best way.

encoded in vba ... only basic testing:

 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 

USING:

(in the cell)

= F (c9-D9)

Answer 4

Here is another formal option.

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

Converts every time to a series of 1/4 hours (* 96), rounds, adds results and scales to hours (* 4)