5 Replies Latest reply on Nov 12, 2009 12:32 PM by philmodjunk

    calculating months registered

    cdharris

      Title

      calculating months registered

      Post

      I have a database of student data.  One of the fields is the date on which the student registered for their degree programme.  I would really like is for Filemaker to do a calculation for me showing how many months have passed since that registration date and automatically update a 'months registered' field.  

       

      At the moment I have a really contrived workaround, but it would be fantastic if there was an elegant way of doing this that doesn't require manual input from me every month.

       

      I'm on Filemaker 9 if that makes any difference.

       

      I really appreciate your help -- thanks in advance.

       

      Chris 

        • 1. Re: calculating months registered
          philmodjunk
            

          Let ( [ Rdate = YourRegisterDateField; today = get (currentdate) ] ;

                  Month(today) + (Year(today)-Year(Rdate)) * 12 - Month(Rdate) - ( Day (Rdate) < day (today) ) )

                

           

          The challenge here is that a "month" isn't the same number of days each time. The above calculation assumes that if the current date is even one day of the month less than the day of the month in the registration date, the current month doesn't count as a complete month.

          • 2. Re: calculating months registered
            cdharris
              

            You're right that it produces some funny results … I just created a new record with a registration date of 01/07/2009 and the months registered field calculates 3 months but should say 4 months.  

             

            I've taken out the last part of the equation, which I think gives me the same result as my workaround (which isn't accurate to the day but is close enough):  

             

            Let ( [ Rdate = YourRegisterDateField; today = get (currentdate) ] ; 

            Month(today) + (Year(today)-Year(Rdate)) * 12 - Month(Rdate) )

             

            So that's fantastically helpful -- thanks Phil. 

            • 3. Re: calculating months registered
              philmodjunk
                

              "I just created a new record with a registration date of 01/07/2009 and the months registered field calculates 3 months but should say 4 months. "

               

              That's because the calculation was designed not to count the current month until the current date's day exceeds the day of the registration month. It's a case of whether or not you want to count the current partial month as a full month or not. Taking out that clause counts the current partial month as a month.

               

              Consider this example: (MM/DD/YY format)

               

              Registration date: 10/31/09

              Current date: 11/1/09

               

              My original expression returns 0 months as only one day has passed.

              Your modified version will return a count of 1 month.

              • 4. Re: calculating months registered
                jlitchford
                  

                I'm trying to do something similar, I have a StartDate and an EndDate, and need to only know the number of months. The StartDate and EndDate will always be in monthly increments (i.e. 1/01/2009...12/01/2009, 1/15/2009...5/15/2009, etc.

                 

                TIA

                 

                jason 

                • 5. Re: calculating months registered
                  philmodjunk
                    

                  then

                   

                  Month(EndDate) + (Year(EndDate)-Year(StartDate)) * 12 - Month(StartDate)

                   

                  Should do the trick.