TimeRound ( theTime ; increment ; forceUpDn )

Rounds a time field by a time interval and allows optionally forcing the rounding up or down

  Average rating: 4.2 (38 votes) Log in to vote

Jonathan Mickelson - Show more from this author

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Function: TimeRound ( theTime ; increment ; forceUpDn )

Description: This function rounds a time field by a dynamic incriment
value, with the option to force it to round up or down, or
by default it uses normal rounding. Normal rounding defines
the breakpoint at 50% of the incriment value.

Output Format: a time value representing the rounded time.

See the following examples:
- TimeRound ( "10:25:00" ; "00:10:00" ; "" ) = "10:30:00"
- TimeRound ( "10:24:59" ; "00:10:00" ; "" ) = "10:20:00"
- TimeRound ( "00:03:59" ; "00:01:00" ; -1 ) = "00:03:00"
- TimeRound ( "03:00:02" ; "01:00:00" ; 1 ) = "04:00:00"
- TimeRound ( "03:00:02" ; "01:00:00" ; "up" ) = "04:00:00"
Parameters:

timeField - A time value to round.

increment - A time value representing the interval to round by. A Null, "" or
0 value will use Normal rounding. A positive number will force
the rounding to round up to the next highest increment, a negative
number will force the rounding to drop to the next lowest increment.

forceUpDn - A number value (or one of two thee values; up, dn or down )
representing desired rounding type to be used. A Null, "" or
0 value will use Normal rounding. A positive number, or "up", will
force the rounding up to the next highest increment, a negative
number, or "dn" or "down", will force the rounding to drop to
the next lowest increment.

Author - Jonathan Mickelson
Last Modified: 12/3/2012
- Wrapped both time-based inputs in GetAsTime (), to better type the inputs for the rest of the calc.
- Pre-processing short circuit to bypass empty times so it doesn't force 12:00 am.

Comments

		Jeff Hough, Portland, OR Apr 4, 2012
There is an error in the incr input variable. It should read into the function as date. For example, if you use the function as is with the incr input of "00:10:00" the value of typeAdj will be 500. If the input is GetAsTime ("00:10:00") the value of typeAdj will be 300. This correctly rounds the result. The change is in the initialization of p2. The updated calc appears below: // TimeRound ( theTime ; incriment ; forceUpDn ) Let ( [ p1 = theTime ; p2 = GetAsTime ( incriment ) ; // <-- THIS IS THE UPDATED PART - JH p3 = Lower ( ForceUpDn ) ; time = p1 ; inc = Case ( IsEmpty ( p2 ) ; GetAsTime ( "00:00:01" ) ; p2 ) ; type = Case ( IsEmpty ( p3 ) ; 0 ; Substitute ( p3; [ "up" ; "1" ] ; [ "dn" ; "-1" ] ; [ "down" ; "-1" ] ) ) ; typeAdj = Case ( type > 0 ; inc * .99999999 ; // Round Up type < 0 ; inc * 0 ; // Round Down type = 0 ; inc * .5 // Round Normal ) ; rnd = ( Int ( ( time + typeAdj ) / inc ) ) * inc ; result = GetAsTime ( rnd ) ] ; Case ( not IsEmpty ( time ) ; // RESULT result ) // END CASE ) // END LET

		Jonathan, Van Nuys, CA Dec 3, 2012
Thanks Jeff, I always tend to use this with actual Time fields, so I'd never run into that! I've updated the calc and wrapped both time-based inputs in GetAsTime (), since both require time values. Best, Jonathan

		Israel Golding, Lakewood, NJ Jun 3, 2015
NOTE: you have "increment" misspelled!

Log in to post comments.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: