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
Thanks in advance!
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”))
Thanks again in advance!
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”))
)
)
Hello,
This is very close to what I am trying to do.
The difference is I want to round to the "nearest 15 min. not round up…is there a way to do that?
@llheath
You can use the same expression above with changing CEILING function to FLOOR
You’d have to use both, depending on the time stamp.
So if it’s 3 hrs 5 min, you want to round that to 3 hours…
And similarly if it’s 3 hrs 8 min, you want to round that to 3.25 hours
Right?
It seems like that is always rounding down.
For instance 8:36 should round to 8:30.
8:40 should round to 8:45.
Correction:
When I clock 01:46:00 it rounds to 01:60:00 - should be 02:00:00:
Works perfect!
Thanks all for your help!
-Joe
You’re welcome
I want all the times to be in 15 min increments. So 3 hours 8 minutes would be 3:15.
3 hours 7 minutes would be 3:00
3 hours 10 minutes would be 3:15 etc.
This excel formula works if c2 is my hours/minute field. I can’t use Round in appsheet with a time field.
=ROUND(C2*(2460/15),0)/(2460/15)
Try:
(
("000:00:00" + HOUR([Duration]))
+ INDEX(
LIST(
"000:00:00",
"000:15:00",
"000:30:00",
"000:45:00",
"001:00:00"
),
(ROUND(TOTALMINUTES([Duration] - HOUR([Duration])) / 15.0) + 1)
)
)
that is it! Thank you.
User | Count |
---|---|
43 | |
28 | |
24 | |
24 | |
13 |