Help with Expression Please

Joe_Seiler · 01-27-2020 07:04 AM

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!

Marc_Dillon

Marc_Dillon

Maybe something like this:

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

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

)

Joe_Seiler

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!

LeventK

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”))
    )
)

llheath

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?

LeventK

@llheath
You can use the same expression above with changing CEILING function to FLOOR

Joe_Seiler

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?

llheath

It seems like that is always rounding down.
For instance 8:36 should round to 8:30.
8:40 should round to 8:45.

Joe_Seiler

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

Joe_Seiler

Works perfect!
Thanks all for your help!
-Joe

LeventK

You’re welcome

llheath

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)

Steve

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)
  )
)

llheath

that is it! Thank you.