Jump to content

Need a formula for rounding up and down a time


Ukyo
 Share

This topic is 5782 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

Told you i wasn't a mathematician :B

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 :B

As for Ukyo:

Glad i could help :

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

)

)

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

This topic is 5782 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.