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

[Joe_Seiler]

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

[Suvrutt_Gurjar]

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

By @Steve

FAQ: Rounding DateTime, Duration, and Time values

Round DateTime value to nearest 5 minutes Round Duration value to nearest 5 minutes Round Time value to nearest 5 minutes

By @GreenFlux

Set Initial Value of TIME column to nearest 15 minute interval

This one took me a minute to figure out so I figured I’d share here to save someone else the time. I needed a TIME column with an Initial Value rounded up to the nearest 15 minutes. TIME( TIME(HOUR(NOW()-"0:00")+"1:00")- IFS( MINUTE(NOW()-"0:00")>45,"0:00", MINUTE(NOW()-"0:00")>30,"0:15", MINUTE(NOW()-"0:00")>15,"0:30", MINUTE(NOW()-"0:00")>0,"0:45" ) ) NOW() is a time but HOUR() and MINUTE() take a duration as input, so subtracting “0:00” converts the data …

[Steve (Steve Coile)]

FAQ: Rounding DateTime, Duration, and Time values

Round DateTime value to nearest 5 minutes Round Duration value to nearest 5 minutes Round Time value to nearest 5 minutes

[Joe_Seiler]

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?

[Steve (Steve Coile)]

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?

[Joe_Seiler]

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)