4 Replies Latest reply on May 9, 2010 7:47 PM by k7rks01

    Calculating dates



      Calculating dates


      Hi Filemaker Forum,


      I am looking for some help calculating the difference in months between 2 dates. I am using Filemaker Pro Adv 10.


      I have 3 fields.


      1st field is drop down date commencement.

      2nd field is drop down date cancellation.

      3rd field I want to be able to calculate the difference between field 1 & 2 to round up to a complete month.


      i.e.         19/07/2009            09/07/2010                  12


      I am not sure if it is possible for Filemaker to calculate the difference as a number when the 2 defining fields are xx/xx/xxxx to begin with.


      Any help would be greatly appreciated,



        • 1. Re: Calculating dates

          It's certainly possible - but you need to clarify the rounding rule. Months come in varying lengths, and you need to decide when exactly does a "complete month" elapse. For example, starting at Jan 31, when does one complete one month?

          • 2. Re: Calculating dates

            Hi Comment,


            That is a good point about the rounding that I didn't actually think about.


            Can a filemaker calculation take the average of 365 / 12 = 30.4?

            Or is it clever enough to make the calculation to include how many days there are in each month?

            i.e  January 31

                  February 28

                  March 31


            I have seen a calculation in a template that comes with filemaker that determines the quarter of the year it falls in based on the month on the drop down calenders. This calculation formula includes all 12 months followed by a 1,2,3,4 based on the quarter. Is this formula something that can be used as a basis for what I am trying to do?


            Thanks for the help.



            • 3. Re: Calculating dates

              I believe Filemaker can do any date calculation you can think of (at least I haven't come up against one that couldn't be done yet). The problem here is not "how", but "what".


              If you can define the rules in a way that would enable a person to get the corect result using paper and pencil (or perhaps a calendar and fingers in this case), it can be done in Filemaker.




              As an example, this calculates the difference in months, ignoring the day of month =


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

              • 4. Re: Calculating dates

                Hi Comment,


                Works like a treat. Exactly what I was after.


                You are a superstar


                Many Thanks,