8 Replies Latest reply on Sep 29, 2011 10:49 AM by GeoffreyMartin

    Rounding times...

    GeoffreyMartin

      Title

      Rounding times...

      Post

      Hello,

      I'm trying to set up a calculation field that will output a time rounded up to the next minute.  The time is expressed in 24 hour format.  The if the seconds go into the next minute, it is always rounded to the next minute.

      from 12:01:01 -> 12:01:59 would equal 12:02:00

      Any ideas?

       

      Thanks in advance.

      -Geoff

       

        • 1. Re: Rounding times...
          philmodjunk

          Let ( T = TimeField ; Time ( Hour ( T ) ; Minutes ( T ) + ( Seconds ( T ) > 0 ) ) )

          • 2. Re: Rounding times...
            GeoffreyMartin

             Let ( T = Time_At_Scene_avl_calc ; Time (Hour (T) ; Minutes (T) + (Seconds (T) > 0 ))).  It accept it and it stops on 'Minutes' and says "This Function cannot be found."

            • 3. Re: Rounding times...
              philmodjunk

              Typo on my part. I should be Minute, not Minutes.

              • 4. Re: Rounding times...
                GeoffreyMartin

                That was weird though because I looked up the time function in the list and the example in parentheses is minutes not minute.  Now it highlights one of the closed parenthesis and says "There are too few parameters in this function."

                • 5. Re: Rounding times...
                  philmodjunk

                  How silly of me to leave out the seconds parameter:

                  Let ( T = Time_At_Scene_avl_calc ; Time (Hour (T) ; Minute (T) + (Seconds (T) > 0 ) ; 0 ))

                  In the help article, Minutes identifies the value to be used in that spot, not the minute function which we are using to extract minutes from the time field..

                  • 6. Re: Rounding times...
                    GeoffreyMartin

                     That worked.  Can you give me a brief explanation of what it's doing?  I want to learn the how not just the what, if you don't mind.  Also in the function list on the calculation window the Time function looks like this: Time (Hours, Minutes, Seconds).

                    Thanks again PhilModJunk!

                    -Geoff

                     

                    • 7. Re: Rounding times...
                      philmodjunk

                      Time is stored in FileMaker as an integer that records time in seconds. That's why you can subtract two time fields and get the elapsed time in seconds without doing any special conversion operations. The time you see in hours, minutes and seconds is just special formatting used to make the internally stored integer a value that is intelligible to humans. Date fields work in much the same fashion, but mark time in days instead of seconds.

                      The time function allows you to create that internally stored integer from specified values for the hour, minute and second. that's what you are seeing in the calculation window. It's identifying what value to put in each "parameter" for the time function. 4:00:00 am could be returned by this function if you used: Time ( 4 ; 0 ; 0 ).

                      If you look up time functions in help, you'll find that the Hour(), Minute() and Second () functions extract just that portion of the value from a time field. In our case, Hour is being used to extract the hours and Minute is being used to extract the minutes from a given value in a time field. Since you want seconds to be 0, the seconds parameter is just that, 0. The only adjustment we need to do is to add one to minutes if there are any seconds recorded in this time field. That's where + (Seconds (T) > 0 ) comes in. If Seconds ( T ) returns any value greater than 0, this expression evaluates as "true". This is called a boolean value and a boolean value of true is the same as a 1. In FileMaker, 0 or an empty value are the same as False. Thus, this expression adds one if there are any seconds and adds zero if there aren't.

                      • 8. Re: Rounding times...
                        GeoffreyMartin

                        Thank you so much.  I had all of the pieces and your explanation stitched them together quite nicely.  I think where I got hung up is that you were using the Hour, Minute, and Second functions within the Time function.  Brilliant.  Thanks again for this!

                         

                        -Geoff