Ceiling on duration, to the nearest 15 min increment, with 1 hour minimum - help please

Hello,

Can someone help me with this?

I have this formula in a google sheet, but am trying to get everything to reside in appsheet.

=CEILING(N3),“00:15”),“1:00”)

This formula looks at a cell, N3 for instance, that has a duration type, like 2:46

It them rounds the new cell up to the nearest 15 min increment, with a 1 hour minimum

So for instance,

00:02 would resolve 1:00:00
00:59 would resolve 1:00:00
01:01 would resolve 1:15:00
01:13 would resolve 1:15:00
01:15 would resolve 1:15:00 – This one is important, it can’t resolve up to 1:30:00
01:16 would resolve 1:30:00
etc.

Any idea where to start with this?

Thanks in advance

Please take a look at the two very useful relevant posts in the Tips and Tricks section

By @Steve

By @GreenFlux

2 Likes
1 Like

Thanks everyone!
Steve, I used your strategy to round DateTime
Here’s the expression I used:

(TIME(CONCATENATE(“0:”,(ROUND(TOTALSECONDS([T&M: Total Time Hr, Min]-HOUR([T&M: Total Time Hr, Min]))/60.0/15.0)*15),":0"))-“00:00:00”+HOUR([T&M: Total Time Hr, Min]))

So, that does work pretty well, however, this is rounding to the nearest 15 minute interval, and not the ceiling of the 15 min interval.
So right now:
1:01 rounds to 1:00
1:08 rounds to 1:15

I am looking for the ceiling of the 15 minute interval like this:
1:00 rounds to 1:00
1:01 rounds to 1:15
1:15 rounds to 1:15
1:16 rounds to 1:30

Any thoughts?

My example post was explicitly for rounding, not ceiling. Have you considered what in your expression might need to change to get the CEILING() rather than the ROUND()-ed value?

3 Likes

Well, thanks for pointing out the obvious, lol
Sorry, about that, and thanks for your teaching method.
Here’s the final, adding the 1 hour minimum as well.

IF([T&M: Total Time Hr, Min]>=“001:00:00”, (TIME(CONCATENATE(“0:”,(CEILING(TOTALSECONDS([T&M: Total Time Hr, Min]-HOUR([T&M: Total Time Hr, Min]))/60.0/15.0)*15),":0"))-“00:00:00”+HOUR([T&M: Total Time Hr, Min])), 001:00:00)

3 Likes