How to ROUND to whatever interval you want.

[Marc_Dillon]

ROUND only rounds to the "ones" decimal place.

Or to describe it another way, it rounds the "tenths" spot up or down.

In order to round to some other interval, we need to first apply a transformation to our number to convert that interval into the ones place. If you want to round to the nearest 1/4, we multiply by 4 to convert every quarter into its own individual value in the ones place. e.g. 0.25 => 1, 0.50 => 2 , 0.75 => 3 , 1.0 => 4. Then we apply ROUND, then we reverse the transformation.

= ROUND( [decimal] * 4 ) / 4.0

The only additional complexity here, for Appsheet, is that we need to make sure to divide by a decimal value in order to get a decimal result, since ROUND will always spit out an integer, and we don't want integer division. Thus divide by 4.0 instead of just 4.

The same principle applies for whatever interval you want to round to.

Round to the nearest tenths place? ROUND( x * 10 ) / 10.0

Round to the nearest 1/3? ROUND( x * 3 ) / 3.0

Round to the nearest 10? ROUND( x / 10.0 ) * 10

Round to the nearest 47? ROUND( x / 47.0 ) * 47

etc...

Rounding time values?

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-Rounding-DateTime-Duration-and-Time-values/m...

[KGingeri]

Being a programmer (man do I wish AppSheet had variables!), I know rounding no problem, although this threw me! 

---

@Marc_Dillon wrote:

The only additional complexity here, for Appsheet, is that we need to make sure to divide by a decimal value in order to get a decimal result

---

I also had a heck of a time re-representing rounded times (in duration).  This was my final solution (to nearest 15 minutes):

  IF(Number(Index(Split([Duration],":"),2))+14.0 > 59,
        Text(Number(Index(Split([Duration],":"),1))+1),
        Text(Number(Index(Split([Duration],":"),1)))
      )
  &IFS(
        Number(Index(Split([Duration],":"),2))+14.0 > 59, ":00",
        Number(Index(Split([Duration],":"),2))+14.0 > 44, ":45",
        Number(Index(Split([Duration],":"),2))+14.0 > 29, ":30",
        Number(Index(Split([Duration],":"),2))+14.0 > 14, ":15",
        Number(Index(Split([Duration],":"),2))+14.0 < 15, ":00"
      )

[Marc_Dillon]

To follow my approach in this thread for rounding to the nearest 15 minutes, you'd start with this as the base:

ROUND( TOTALMINUTES( [Time] ) / 15.0 ) * 15

Now if we were in javascript or gsheets or something where all time is represented by decimals where 1=24 hours, we could just simply divide that by 60.0 and 24.0, but this is Appsheet where date and time things are overly complicated 😆...

So we'll just build the time string from its component parts, kind of like how you did. We can take the above result and divide by 60 to get the hour component, then take the above result again and MOD() it by 60 to get the remaining minutes. In total:

           ROUND(TOTALMINUTES( [Time] ) / 15.0)*15  / 60   & ":" &
MOD(ROUND(TOTALMINUTES( [Time] ) / 15.0)*15  , 60 ) & ":00"

[KGingeri]

Excellent, thanks @Marc_Dillon!!  I totally forgot about MOD() - and didn't notice AppSheet has it.  I needed to modify a bit as I had set this as a text field:

TEXT( ROUND(TOTALMINUTES([Duration])/15.0)*15/60 ) & ":" &
RIGHT("0"&TEXT( MOD(ROUND(TOTALMINUTES([Duration])/15.0)*15, 60) ), 2) & ":00"

 I'll maybe try to set is as a DURATION type to see if I can get that working.

[KGingeri]

OK so field type DURATION seems to work the same @Marc_Dillon 👍 however, here's something else I keep bumping into (I just did a few posts on calculating the amount to charge as well related to this)
So even tho the test runs fine and give me correct results, what the app renders is different! 
Any insight would be greatly appreciated?!...

BTW, I'm ignoring seconds on the field so I dropped the final '& ":00"' in the formula as well.

[KGingeri]

OK, I think I found a work around for this as well...
I set the field to a decimal type and just rendered that in the app with the following formula/expression:

ROUND(TOTALMINUTES(([Stop]-[Start]))/15.0)*15/60.0

It's pretty obvious that AppSheet does not handle converting "time-represented" values properly.  Maybe I'm approaching too many things as a programmer? 

In the end, whether the time spent (and rounded) shows as hh:mm or h.m isn't that big a deal.  🙂

I was also able to eliminate my extra [Duration] field and just use [Stop]-[Start] instead.