3 Replies Latest reply on Nov 20, 2013 10:44 AM by andruziwa

    calculating the difference between times

    andruziwa

      Could anyone offer some suggestions for how to calculate the difference between times?

       

      I have played around, searched around the internet, searched BrianDunning.com for custom functions and failed.

       

      I want to see if appointment records for the same day are greater than 30 minutes or 60 minutes behind the current time. I am using this to conditionally format a portal list of appointments to highlight appointments that are supposed to have happened.

       

      I am currently using the expression: get (current time) - local_appts::appointment time > 0 to successfully highlight appts that are past due but I cannot differentiate between ">0" and ">30" or ">60."

       

      Thank you in advance for any advice on this.

        • 1. Re: calculating the difference between times
          Mike_Mitchell

          Hello, andruziwa.

           

          What you'll want is the Case function. This will allow you to set up a series of conditions, along with their outcomes if true. For example:

           

          Case (

            Get ( CurrentTime ) - local_appts::appointment time > 60 ; ">60" ;

            Get ( CurrentTime ) - local_appts::appointment time > 30 ; ">30" ;

            Get ( CurrentTime ) - local_appts::appointment time > 0 ; ">0" ;

            ""

          )

           

          This tells FileMaker to choose the first true condition. (That's why they're in the order they're in.)

           

          For better (faster) execution, use the Let function to call the Get ( CurrentTime ) function and evaluate the related field only once:

           

          Let ( [

            rightNow = Get ( CurrentTime ) ;

            apptTime = local_appts::appointment time

          ] ;

           

          Case (

            rightNow - apptTime > 60 ; ">60" ;

            rightNow - apptTime > 30 ; ">30" ;

            rightNow - apptTime > 0 ; ">0" ;

            ""

          )

          )

           

          HTH


          Mike

           

          Edit: For Conditional Formatting purposes, you can put the same conditions in. Just remember that Conditional Formatting tests are executed in the order they appear, and all true tests are applied. So you'll want to put the tests in the reverse order of the Case statement above. That way, the >60 test will evaluate last and whatever formatting you apply through it will be sure to appear.

          • 2. Re: calculating the difference between times
            Mike_Mitchell

            Duh. Missed the "minutes" part. Pixi is right; time calculations work in seconds, so you have to multiply your minutes by 60 to get the correct answer.

             

            Mike

            • 3. Re: calculating the difference between times
              andruziwa

              thanks to both of you for this rapid and awesome answer.  much appreciated.