 Hello,
In my google sheet, I current use this expression to round a time entry to the nearest 15 minute increment, with a 1 hour minimum. Any idea how to move this expression over to appsheet?

=if(B1>=1, CEILING(A1,“00:15”),“1:00”)

The A column is a duration - 6:32
The B column is that same duration converted to decimal - 6.53

What currently happens is I take the ceiling based on 15 minute increments of the A column - 6:32 would be 6:45
The B column is just a qualifier. If it’s less than or equal to 1, it sets the 1 hour minimum

1 Like

Maybe something like this:

IF( [A] < 001:00:00 , 001:00:00 ,

“000:” & Hours([A]) & “:” & (CEILING(Minutes([A])/15) * 15)

)

Thanks for your help. I had to tweak it a little, and It’s almost there.

When I clock 01:01:00 it rounds to 01:15:00 - perfect
When I clock 01:16:00 it rounds to 01:30:00 - perfect
When I clock 01:31:00 it rounds to 01:45:00 - perfect
But
When I clock 01:46:00 it rounds to 01:60:00 - should be 02:00:100:

Here’s what I’m using now:

IF([T&M Total Time (H,M)] <= “001:00:00”, 001:00:00, HOUR([T&M Total Time (H,M)]) & “:” & (CEILING(MINUTE([T&M Total Time (H,M)])/15) * 15 & “:” & “00”))

Correction:
When I clock 01:46:00 it rounds to 01:60:00 - should be 02:00:00:

Can you try with this?

``````IF(
[T&M Total Time (H,M)] <= “001:00:00”,
001:00:00,
IFS(
CEILING(MINUTE([T&M Total Time (H,M)])/15) * 15 = 60,
HOUR([T&M Total Time (H,M)]) + 1 & “:00:00",
TRUE, HOUR([T&M Total Time (H,M)]) & “:” & (CEILING(MINUTE([T&M Total Time (H,M)])/15) * 15 & “:” & “00”))
)
)
``````
2 Likes

Works perfect!