Brian Dunning's FileMaker Custom Functions

QuarterRoundTS ( mytimestamp )

Rounds a timestamp to the nearest quarter hour.

  Average rating: 4.5 (31 votes) Log in to vote

Jeff Morrison   Jeff Morrison - Show more from this author

Share on Facebook Share on Twitter

  Sample input:
QuarterRoundTS (4/14/2008 11:58:00 PM )
  Sample output:
4/15/2008 12:00:00 AM

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

I needed a way to hand the function a timestamp and get back one rounded to the nearest quarter hour. There were plenty of good functions for rounding to the quarter hour, but none that would take in a timestamp and hand back a timestamp. It also needed to take into account when the day changes due to the rounding of the time.

Example:
Insert Calculated Result [ after_field ; QuarterHourTS ( before_field )]

In this example, the "before_field" and the "after_field" should be timestamp fields.

 

Comments

ducklofty   ducklofty, Edinburgh, Scotland
Jan 9, 2012
By adding a second variable of SecondsToRound and find/replacing 900 with the variable name this becomes a generic timestamp rounding function

eg 60 seconds to nearest minute, 300 second to nearest 5 mins, 3600 to nearest hour
 
caitlin   caitlin
Mar 21, 2020
As per ducklofty post:

RoundTS ( mytimestamp ; sec)

If(
GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / sec ; 0 ) * sec ) = "24:00:00"; Date ( Month ( mytimestamp ) ; Day ( mytimestamp ) ; Year ( mytimestamp ) ) + 1 & " " & "12:00:00 AM";

Date ( Month ( mytimestamp ) ; Day ( mytimestamp ) ; Year ( mytimestamp ) ) & " " &

Case(
Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / sec ; 0 ) * sec ))=0; 12;
Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / sec ; 0 ) * sec )) > 12; Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / sec ; 0 ) * sec )) - 12;
Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / sec ; 0 ) * sec ))) &

":" & Right("00" & Minute(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / sec ; 0 ) * sec )); 2) & ":00" &

If(Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / sec ; 0 ) * sec )) >= 12; " PM"; " AM")
)
 

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: