ROUND UP HOURS to the next quarter hour

[SueSea]

Any suggestions on how to round up a decimal to the next quarter hour?

Time IN=A
Time OUT=B
ACTUAL TIME =C
ACT.TIME to DECIMAL=D
ROUND Up to quarter hour (in decimal)=E

A is formated as h:mm AM/PM
B is formated as h:mm AM/PM
C is formula +B1-A1; formated as hh.mm
D is formula =INT(I18*24)+MOD(I18*24,1)*60/100

I have tried the following for E:
=ROUNDUP(d1/0.25,0)*0.25

but where D is 0.20 E = .25 I would like 20 muniutes to round up to .50 hour

Or if D is
from 0.00 to 15.0 minutes then E = .25
from 16.0 to 30.0 minutes then E = .50
from 31.0 to 45.0 minutes the E = .75
from 46.0 to 60.0 minutes the E = 1.0


Thanks

 

[David Biddulph]

I don't know what your strange formula for column D is trying to do, but if
you want to change your hh:mm in column C to decimal hours the formula is
=C1*24, and the ROUNDUP in E could use either your formula or
=CEILING(D1,0.25)

I think your confusion is that you are pretending that 20 minutes is 0.20
hours, but of course it is 0.3333

 

[Conan Kelly]

SueSea,

Use the MROUND() function.

=MROUND(D1,0.25)

(In XL 2002/2003, MROUND() is part of the Analysis Tool-Pak add-in...don't
know about XL 2007)

OOOHHHH, wait a minute....rereading your post, I see you want everything to
round up to the next quarter hour. Using my solution, 7 mins will round
down to 0.00, 8 mins to 14 mins will round up to 0.25, 16 mins to 22 mins
will round down to 0.25, 23 mins to 29 mins will round up to 0.5, etc... So
maybe MROUND() won't work for you. I don't know why you would want to round
16 mins upto 1/2 hour.

Something else I noticed, where did column I come into play...specifically
cell I18. You say "D is formula =INT(I18*24)+MOD(I18*24,1)*60/100". What
is the formula in cell I18? Just out of curiosity, why did you use this
formula. I'm not going to bother picking this formula apart to see what it
is doing, but why couldn't you just use "=I18*24". Is there some reason you
had to use this formula? At first glance, it looks like it might be doing
the same thing as "=I18*24", but once again, I'm not going to take the time
to analyse your formula to see what the differences are.

HTH,

Conan