5 Replies Latest reply on Nov 1, 2010 9:39 AM by philmodjunk

    date range calculation

    gug

      Title

      date range calculation

      Post

      Can someone tell me what is wrong with this formula?:

      Case ( date <1/1/2008 ; Ed net * .06; date  < 7/1/2010 ; Ed net * .05; date ≥7/1/2010 ; Ed net * .12; Ed net * 0 )

      No matter what the date of the record, the formula delivers a result based on the third case. In other words it uses .12 as the multiplyer regardless of the date. The database has records with dates as old as 2006 and as new as a few days ago, so all three cases are in fact found in the database.

      Filemaker accepts the formula, so structurally it seems fine. All the fields are properly referenced. I've rebooted and restarted.

      ?????

      Thanks. I appreciate your help.

        • 1. Re: date range calculation
          sunmoonstar.13

          Use the Date function to format the dates:

           

          Case (

          date < Date ( 1 ; 1 ; 2008 ) ; Ed net * .06 ;

          date < Date ( 7 ; 1 ; 2010 ) ; Ed net * .05 ;

          date ≥ Date ( 7 ; 1 ; 2010 ) ; Ed net * .12 ;

          0 )

           

          And make sure that the Storage Options for the calculation field are set to "Do not store calculation results..."

           

          Nick

           

          • 2. Re: date range calculation
            LaRetta_1

            There is no need to set this calculation to 'Do not store calculation results'  unless the field 'date' is Get ( CurrentDate).  The evaluation for this calculation takes place within the same record and it will re-evaluate whenever the date field changes.  :^)

            And since all dates are covered by the fist three criteria, you will never get the 0 result so it can be shortened to:

            Case (
            date < Date ( 1 ; 1 ; 2008 ) ; Ed net * .06 ;

            date < Date ( 7 ; 1 ; 2010 ) ; Ed net * .05 ;
            Ed net * .12
            )

            • 3. Re: date range calculation
              gug

              Thanks so much, I'll try this.

              • 4. Re: date range calculation
                gug

                It worked. I've never used the date format for dates and got away with my homemade versions in "if" formulae.

                Thanks for your help!

                Bill

                • 5. Re: date range calculation
                  philmodjunk

                  For anyone else reading this thread, 7/1/2010 doesn't get identified by FileMaker as a date, it's identified as a two division expressions, 7 divided by 1 divided by 2010 which is why you should use the date function instead.