2 Replies Latest reply on Sep 23, 2013 2:40 PM by Atkins

    Trouble with Time Calculation

    Atkins

      Title

      Trouble with Time Calculation

      Post

           Hello,

           I believe what I'm trying to do should be fairly simple, and I've used several templates and the help file to double-check my work, yet I'm still not getting the desired results.  This is easy:

             
      •           I have a "Time" field that is an auto-enter calculation that grabs the time from a timestamp upon import.  This WORKS and I have verified the data in this field to be in the correct time format (24hour).
      •      
      •           I have a Calculation field where I'm trying to calculate what time of day the Time field is.  I've attached a screenshot of this calculation for your reference.

           Currently all of the records/times are producing result number 1, "Morning".  No matter what I put in I only get the first result back.  I've tried formatting my equation any and every way I can think of with no luck.  Why does this not work?  Any help is greatly appreciated!

            

      sc1.jpg

        • 1. Re: Trouble with Time Calculation
          philmodjunk

               T1 < Created_Time < T2 is incorrect syntax. Rewrite it to be:

               T1 < Created_Time AND Created_Time < T2

               but you can simplify the case function's expression further by writing it like this:

               Case (
                         Created_Time < T1 ; "Other" ;
                         Created_Time < T2 ; "Morning" ;
                         Created_Time < T3 ;  "Afternoon" ;
                         "Evening"
                        )

               Case functions go with the first expression to evaluate as true so as long as you test the field against ever larger or ever smaller values, you don't have to use the full range expression each time.

               Note that the times T0 and T4 are not needed. Time ( 0 ; 0 ; 0 ) is the smallest value a time field can have and Time ( 23 ; 59 ; 59 ) is the largest possible creation time that might be auto-entered.

          • 2. Re: Trouble with Time Calculation
            Atkins

                 Works like a charm!  Thanks for the helpful tips.  It's always good to learn something, especially a simpler way of doing things...

                  

                 Is there a good place to reference syntax issues like the one I had?  The way I was writing it was a logical mathematical formula, but it makes sense that FileMaker doesn't like that syntax.  How would I have known that?  I had already looked through the Help pages for Time ( ) functions, etc...

                 Thanks again!