7 Replies Latest reply on Sep 6, 2014 8:47 AM by biggorilla

    Date is in range Calculation

    biggorilla

      Question - I'm trying to write a calculation that would determine if a date is within a certain range - (ie Sep 1, Current Year - Dec 31, Current Year).  I've tried with a Case Statement and also with an If Statement.  The calculation field is looking at a related table of parent records.

       

      If (Date ( 9 ; 1 ; Year ( Get ( CurrentDate ) )  ≤ families_LOGHOURS::BacktoSchoolNight_Attendance ≤ Date ( 12 ; 31 ; Year ( Get ( CurrentDate )) ; "Yes"; "No")))

       

      I keep getting the "too many parameters" error dialogue

       

      Thanks for the help!

       

      Alberto

        • 1. Re: Date is in range Calculation
          raybaudi

          Let ([

          F = families_LOGHOURS::BacktoSchoolNight_Attendance ;

          Y = Year ( Get ( CurrentDate ) )

          ];

          Case (

          Date ( 9 ; 1 ; Y ) ≤ F and F ≤ Date ( 12 ; 31 ; Y ) ; "Yes" ;

          "No"

          )

          )

          1 of 1 people found this helpful
          • 2. Re: Date is in range Calculation
            jbrown

            Good morning.

            Daniele's method is much simpler to read, but your method works as well, with a few adjustments. You did not close the Date function on either side; instead, you put the closing parentheses outside the IF statement. And of course you forgot the AND. You need to break up the statement into two parts and evaluate both. If both are true, then Yes.

             

            What Daniele shows is using the Let function for repeated values. In your case the Year of the current date. Instead of asking the function to find the Year of the current date twice, you type in Y to stand for that value after assigning that value to the variable Y in the LET statement's beginning. And instead of having that long field reference in your case / if statement twice, you have a single variable F for that value.

            .

            Also, in your function the Get (CurrentDate), the Year (), and the Date() functions are all called twice. That's 6 functions being called. What Danielle shows you is fewer calls of functions.

             

            The LET statement is your friend. For my first three years I never used it. Now I can't live without it. I wonder how I went through life without the LET statement.

            1 of 1 people found this helpful
            • 3. Re: Date is in range Calculation
              biggorilla

              Thanks for the prompt answer!  It works great and lasts a long time!

               

              I will be sure to get more familiar with the Let () function.

               

              Alberto

              • 4. Re: Date is in range Calculation
                biggorilla

                I really appreciate the thorough explanation!  It's nice to get this kind of community support.

                 

                Best,

                 

                Alberto

                • 5. Re: Date is in range Calculation
                  flybynight

                  +1 for the Let statement.

                  Hang around here, and you will see extensive use of it. Like others, for the longest time, I just didn't "get it" when it came to using Let. Eventually it clicked: it can make complex formulae so much easier to compose and read. And if you keep referring to a chunk of calculated data over and over in your full calculation (several Case statements, for instance), it can increase performance a little, since it only has to calculate that chunk once.

                   

                  FullCity Consulting had a good post on it, in their "Function of the Week" blog series:

                  http://fullcityconsulting.com/fm-function-of-the-week-let

                   

                  This is a great community! I have learned so much here.

                   

                  Laters,

                  -Shawn

                  • 6. Re: Date is in range Calculation
                    erolst

                    biggorilla wrote:

                    It's nice to get this kind of community support.

                     

                    Now give some back and credit Daniele with the correct answer, instead of yourself … (though I'm sure he doesn't care about that)

                    • 7. Re: Date is in range Calculation
                      biggorilla

                      I clicked on the "correct answer" button. Was I not supposed to that? - didn't know that would give me the credit.  Sorry! I'll take a look to see if I can correct.

                       

                      Alberto Diaz

                      310-310-9996 cell

                      biggorilla@mac.com