5 Replies Latest reply on Jan 1, 2012 1:48 PM by Bruceb

    Rounding Time to Quarter Hour

    Bruceb

      Title

      Rounding Time to Quarter Hour

      Post

      I am creating a timecard entry screen and we need to round the resulting total hours up and down to the quarter hour.   I have hit a brain freeze in the best way to handle this.  Any ideas would be greatly appreciated.

        • 1. Re: Rounding Time to Quarter Hour
          LaRetta_1

          You might play with this example - just adjust the rules as you need, result is TIME and your TotalHours field should be Time as well:

          Round down:

          Time ( Hour ( TimeField ) ; Minute ( TimeField ) ;
          Case (
          Seconds ( TimeField )   <  15 ; 0 ;
          Seconds ( TimeField )   < 30 ; 15 ;
          Seconds ( TimeField )   < 45 ; 30 ;
          45 )
          )

          Round up:

          Time ( Hour ( TimeField ) ; Minute ( TimeField ) ;
          Case (
          Seconds ( TimeField )   > 45 ; 60 ;
          Seconds ( TimeField )   > 30 ; 45 ;
          Seconds ( TimeField )   > 15 ; 30 ;
          15 )
          )

          • 2. Re: Rounding Time to Quarter Hour
            Bruceb

            Thank you.  I realized I posed the question incorrectly, but your response did work on another area. I should have asked about rounding a fraction. I appreciate your reply and input

            • 3. Re: Rounding Time to Quarter Hour
              LaRetta_1

              Hi Bruce, rounding a fraction is easy because it is base 10 but you have not provided example of how you want the rounding.  Have you checked out the Round() function?  Read up on it in your Help and see if it will do the trick for you otherwise provide the rounding rules, example:

              If number is 10.25 ... round down to 10.00
              If number is 10.26 ... round up to 10.50

              ... etc.  With examples on what you expect, the calculation can then be written to fit the rules.  :-)

              • 4. Re: Rounding Time to Quarter Hour

                To round number with a different precision I use the following formula:

                Round(number / Precision ; 0) * Precision

                If you'd like to round any time with a precision of 15 minutes the precision should be set to 900 (which is 900 seconds in 15 minutes).
                So your formula could look  like:

                GetAsTime ( Round( EnteredTime / 900 ; 0 ) * 900 )

                15:34:56 will be returned as 15:30 and 15:38:00 as 15:45

                hope this helps,

                Menno
                • 5. Re: Rounding Time to Quarter Hour
                  Bruceb

                  Thank you, that works great.