QuarterRoundTS ( mytimestamp )
Rounds a timestamp to the nearest quarter hour.
Average rating: 4.5 (31 votes) Log in to vote
|
Jeff Morrison - Show more from this author |
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, 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 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") ) |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.