what is the formula for rounding time up to 1/4 hr?

[Guest]

Does anyone know the formula for rounding up time to the next 1/4 increment?

 

[Rick Rothstein \(MVP - VB\)]

The following formula will do what you asked...

=IF(MOD(A4,TIME(0,15,0))=0,A4,MROUND(A4+TIME(0,7,0),TIME(0,15,0)))

It requires the Analysis ToolPak Add-in to be selected in the Tools/Add-Ins
option on Excel's menu bar.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Just occurred to me... if your time values can have seconds in them, then
use this formula instead,

=IF(MOD(A4,TIME(0,15,0))=0,A4,MROUND(A4+TIME(0,7,29),TIME(0,15,0)))

Rick

 

[David Biddulph]

=CEILING(A1,1/96)

 

[Rick Rothstein \(MVP - VB\)]

And, of course, David's formula is far superior. You may want to use his
formula this way, though....

=CEILING(A1,TIME(0,15,0))

as it will be easier to modify the round-off time (for example, to next 30
minute increment) in the future.

Rick

 

[Guest]

=ROUNDUP(A1*96,0)/96