Brian Dunning's FileMaker Custom Functions

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   Jonathan Mickelson

Share on Facebook Share on Twitter

  Sample input:
TimeRound ( "10:25:00" ; "00:10:00" ; "" )
TimeRound ( "10:24:59" ; "00:10:00" ; "" )
TimeRound ( "00:03:59" ; "00:01:00" ; -1 )
TimeRound ( "03:00:02" ; "01:00:00" ; 1 )
TimeRound ( "03:00:02" ; "01:00:00" ; "up" )
  Sample output:
10:30:00
10:20:00
00:03:00
04:00:00
04:00:00

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

Under construction. Email me your wish list for improvements.