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

AppSheet AppSheet Q&A
1

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

2

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](https://community.appsheet.com/t/faq-rounding-datetime-duration-and-time-values/31686) Tips & Tricks ?

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](https://community.appsheet.com/t/set-initial-value-of-time-column-to-nearest-15-minute-interval/15554) Tips & Tricks ?

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 …

2 Likes
3

FAQ: Rounding DateTime, Duration, and Time values Tips & Tricks ?

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

1 Like
4

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?

5

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
6

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