Does anyone know the formula for rounding up time to the next 1/4 increment?
R Rick Rothstein \(MVP - VB\) Oct 11, 2007 #2 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
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
R Rick Rothstein \(MVP - VB\) Oct 11, 2007 #3 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
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
R Rick Rothstein \(MVP - VB\) Oct 11, 2007 #5 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
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