Need a formula for rounding up and down a time

[Ukyo]

Hi,

Do any of you mathematicians out there can help me with the formula for rounding up and down a time to the nearest quarter hour?

Thanks in advance,

Ukyo

Edited December 4, 2006 by Guest

[Genx]

Well i'm no mathematician ... but:

Let([ x = Get(CurrentTime) ; round = "up" ];

Time( Hour(x) ; Case( Round = "up" ; Ceiling( Minute(x) / 15 ) * 15 ; Round = "down" ; Floor( Minute(x) / 15 ) * 15 ) ; 0 )

)

That should do it - Change Round = "up" to round = "down" if you want to round down to the nearest 15 minutes.

[Genx]

Oh and by the way, Welcome to the forums! :B

[comment]

How about a simple one:

Round ( time / 900 ; 0 ) * 900

[Genx]

Told you i wasn't a mathematician

Though if you want to just make the above almost as simple:

Time( Hour(time) ; Ceiling( Minute(time) / 15 ) * 15 ; 0 )

By the way, where does the number 900 come from?

[Ukyo]

Hi Genx,

Bulls-eye!!

First, let me thank you for your greetings!

Another thanks for saving tons of my time.

I'm really poor on mathematics...

Let function was also great!

Will it also work dyanamically using radio button field at the

round = "down" or "up" portion?

I'll try it anyway right after this reply.

Can never catch-up any of you people in this forum.

All the best,

Ukyo

[Ukyo]

60*15=900 for getting nearest 15 minutes

60*30=1800 for gettng nearest 30 minutes

maybe...

I'm poor on math I told you.

Edited December 4, 2006 by Guest

[comment]

Time( Hour(time) ; Ceiling( Minute(time) / 15 ) * 15 ; 0 )

is not the same. First, it always rounds UP, while Round() rounds to NEAREST. But if you want to ALWAYS round up, you should also round up when time is between 0:00:01 and 0:00:59.

[Ukyo]

It did work dynamically!

up_down: Text (radio button; ValueList(up ¶ down)

---------------------------------------

Let(

[

x = time;

round = up_down

];

//---------------------------------------//

Time( Hour(x) ;

Case(

Round = "up" ; Ceiling( Minute(x) / 15 ) * 15 ;

Round = "down" ; Floor( Minute(x) / 15 ) * 15 ) ;

0 )

)

This was absolutely what I needed!!

I'll also change the 15 portion to a drop down

list to make it more dynamic.

Thanks Genx again!

[Genx]

Comment:

Oh right, fair enough -- just the op asked for the option to round EITHER up or down to the nearest fifteen minutes, as far as i could guess by user option.

Anyway, your method is kind of cool anyway, it's going in my pocket of tricks

As for Ukyo:

Glad i could help :

[Genx]

Btw is that where the 900 comes from -- as Ukyo suggests above?

[comment]

I am not sure my point got across:

0:00:59 rounded UP to the nearest quarter hour is 0:15:00. Your formula returns 0:00:00.

---

And yes, 900 is the number of seconds in 15 minutes. Time in Filemaker is the number of seconds since midnight.

Edited December 4, 2006 by Guest

[Genx]

I suppose you could use:

Let([ x = Get(CurrentTime) ; round = "up" ; mins = (Minute(x) & "." & Seconds(x)) / 15];

Time( Hour(x) ; Case( Round = "up" ; Ceiling( mins ) * 15 ; Round = "down" ; Floor( mins ) * 15 ) ; 0 )

)

But that's just getting cumbersome -- is there anyway to round down with your method?

[Genx]

My bad, don't hurt me it's 1 am, but any way to choose with your method is what i meant.

[comment]

You can make it completely dynamic:

Field Time (time) - the time to round;

Field Direction (text) - either "up" or "down";

Field Precision (number) - the number of minutes to round to (5/10/15/30/etc...)

Let ( [

p = Precision * 60

] ;

Case (

Direction = "up" ;

Ceiling ( Time / p ) * p ;

Floor ( Time / p ) * p

)

)

[Genx]

... How do you always make your calculations so damned simple?

Hmmmm, awesome, thanks for the calc.

[comment]

I'm too lazy for complex...

[Ukyo]

Awesome!

TXS

[Genx]

I'm too lazy for complex...

Lol! Good answer.

[aldipalo]

As I said 2 minutes and out comes a simple (Well I don't know how simple, that's really a relative statement) but elegant formula.

Al

[comment]

Hey, leave me alone - today is 'Pick on Genx' day!

[Genx]

... Hmmm yes, apparently so. Though he was giving you a compliment.

[Ukyo]

Thank you again to Genx, comment and FM Forum,

Since I've solved my problem from this forum,

I will attach a file for people visiting this

post in the future looking for this formula.

Attached file is made on Mac.

[color:red]Deleted attachment. Go to Post#232242 for the latest.

Edited December 7, 2006 by Guest

[Ukyo]

Hi there,

When I have zero for precision, I get the result of "?".

How do I make this to show entered time instead of "?".

time 00:00:59; precision 0; ? to

time 00:00:59; precision 0; 00:00:59

Little more assistance appreciated.

Thank you in advance.

[Ukyo]

Oh yes...

Case (

precision = 0 ;

time ;

Ceiling ( time / precision ) * precision

)

Any other approach?

[comment]

That will work. You could also use:

Let ( [

p = Max ( 1 ; Precision * 60 )

...

(assuming you'd never want to round to a fraction of a second.)

[Ukyo]

Thank you "comment" for respond.

What if I wanted to round a fraction of a second?

Is it possible?

I'll attach my latest modification.

I have changed field precison to

precison - calculation - date

(concatinaton of number fields bellow)

precison_hour - number

precison_minute - number

precison_second - number

I thought it will be easier for user to enter.

Also challenged to use the CustomFunction for my first challenge.

[color:red]

Deleted attachment. Go to Post#232242 for latest.

Edited December 7, 2006 by Guest

[comment]

If you expect the user would want to round to a fraction of a second, then either change the "1" to the smallest fraction allowed, or go with the Case() you have.

Note that it will be more efficient to bypass the entire calculation when precision is 0 or empty:

Case ( Precision ;

Let ( [

p = Precision * 60

] ;

Case (

Direction = "up" ;

Ceiling ( Time / p ) * p ;

Floor ( Time / p ) * p

)

) ; Time )

[Ukyo]

Thank you for the correction of efficiency!

You again inspired me!

Let within a Case... I could never thought out.

Ellegant.

1 to the smallest fraction allowed...hummm??

I need to go back to grade school maybe...

[Ukyo]

I couldn't have solved my problem without the great many helps here, and never this ellegant!

Thanks again "comment"!!

Attached is the fixed version.

Anyone who needs this, just download and leave

thanks to "comment" please.

Edited December 7, 2006 by Guest

[Ukyo]

Forgot to attach!

RoundTime_CustomFunction.fp7.zip

Edited December 7, 2006 by Guest

[Ukyo]

Hope it's O.K.?

Edited December 7, 2006 by Guest