Round up to the next quarter of an hour

[Keyuser]

Hy guys!

Simple question. Need to round up to the next quarter of an hour.

For example:

• 07:40 -> 07:45
• 07:46 -> 08:00
• 07:45 -> 07:45
• 08:02 -> 08:15

My solution:

time(ceil(Time*24/(1/4))*(1/4)/24, 'hh:mm')

This works almost.

But the result for 07:45 is 08:00.

In this case I need the exakt time 07:45.

Thank you!

[sunny_talwar]

There are always going to be Rounding Errors when using Ceil, Floor, or Round function

[sunny_talwar]

When I try this it works

=time(ceil(MakeTime(7, 45)*24/(1/4))*(1/4)/24, 'hh:mm')

[pradosh_thakur]

Hi 

I tried the below. Might look silly but working. 😄

if(mod(right(text(time#('07:45','hh:mm')),2) ,15)=0,text(time#('07:45','hh:mm')), if( right(text(time#('07:45','hh:mm')),2) > 45 ,

left(text(time#('07:45','hh:mm')),2)+1 & ':' & if(ceil(right(text(time#('07:45','hh:mm')),2)/15)=4,00,ceil(right(text(time#('07:45','hh:mm')),2)/15)*15)
,
left(text(time#('07:45','hh:mm')),2) & ':' & ceil(right(text(time#('07:45','hh:mm')),2)/15)*15

))

Replace text(time#('07:45','hh:mm')) with your field. You definitely can optimize the expression.

Thanks

Pradosh

[rwunderlich · Accepted solution]

I think the only way to avoid rounding precision problems is to convert to integer units -- minutes in this case -- do the ceil function and then convert back to Time. 

Time(interval#(ceil(num#(text(interval(mytime, 'm'))),15),'m'))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com