I have this formula in a google sheet that ro...

(Joe Seiler) #1

I have this formula in a google sheet that rounds up to the nearest 15 minute increment in a duration.

Can someone help convert this so i can have appsheet enter the info?

Basically, I am trying to eliminate all formulas in the sheet that appsheet uses.

Here’s what I’m using now:

=CEILING(P4,“00:15”),“1:00”)

(Aleksi Alkio) #2

Do you need to convert an existing value into a different column or do you want to write a validation rule for the input?

(Joe Seiler) #3

Kind of an existing value… I have a start time column, an end time column, and another column that calc’s the duration.

From the duration, another column is calc’d from the duration to decimal.

I need the duration, rounding to the nearest 15 min interval, perhaps on the fly, but it has it’s own column.

Also, the original formula posted wasn’t in full, and probably caused confusion.

The end of it is an else statement.

Here’s the whole formula:

=if(Q2>=1, CEILING(P2,“00:15”),“1:00”)

See attached a screenshot which probably makes more sense than I’m writing.

I do have the P column in the pic coming from virtual columns breaking down hours, minutes, and seconds…so perhaps I can pluck that formula from there.

(Aleksi Alkio) #4

If you want it as a decimal, something like this should give you that… =HOUR([END]-[START]) + (CEILING(MINUTE([END] - [START])/15.0)*15.0)/60.0