2 Replies Latest reply on Jan 15, 2012 1:22 PM by ArthurJoyce

    Calculate interval in y,m,d from set record date to current date

    ArthurJoyce

      Title

      Calculate interval in y,m,d from set record date to current date

      Post

      I'm trying to calculate in years, months, and days how long it is between a date on a past record in FileMaker to the current date.

      I have a calculation field that I thought was working perfectly . . . until I got to 1/1/12 and then the calculation starting going backward. 

      The calculation in the calc field is:

      GetAsText ( Year ( Get ( CurrentDate ) )  - Year ( SermonDate ) - If ( Get ( CurrentDate ) <  Date ( Month ( SermonDate ) ; Day ( SermonDate ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( SermonDate ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( SermonDate ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( SermonDate )  + If ( Day ( Get ( CurrentDate ) )  ≥ Day ( SermonDate ); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( SermonDate ) ; Day ( SermonDate ) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days "

       

      I have a date field that is labeled "SermonDate" and as I said, the above calculation seemed to be working until the beginning of this year. I've tried to find the flaw but I can't. Anyone have an idea what's wrong?

      Thanks for any help given.

        • 1. Re: Calculate interval in y,m,d from set record date to current date
          davidanders

          http://help.filemaker.com/app/answers/detail/a_id/5532/~/calculate-the-age-of-a-person-in-years,-months-and-days

          Calculate the Age of a Person in Years, Months and Days

          The following calculation fields demonstrate how to calculate a person's age. These calculations compare the value in an existing date field called "Birthdate" with the value obtained using the Get(CurrentDate) function.

          Years
          ElapsedYears (calculation, number result) =
          Year ( Get ( CurrentDate ) ) - Year ( Birthdate ) - If ( Get ( CurrentDate ) < Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Get ( CurrentDate ) ) ); 1 ; 0 )

          Months
          ElapsedMonths (calculation, number result) =
          Mod ( Month ( Get ( CurrentDate ) ) - Month ( Birthdate ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( Birthdate ) ; 1 ; 0 ) ; 12 )

          Days
          ElapsedDays (calculation, number result) =
          Day ( Get ( CurrentDate ) ) - Day ( Birthdate ) + If ( Day ( Get ( CurrentDate ) )  ≥ Day ( Birthdate ) ; 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) )  < Day ( Birthdate ) ; Day ( Birthdate ) ; Day ( Get ( CurrentDate )- Day ( Get ( CurrentDate ) ) ) ) )

          Please note that the three calculations listed above are not meant to be used independently. For example, the ElapsedDays calculation does not give you the number of days from one date to another; rather it gives you the remainder after you have calculated the number of years and months. The following formula combines the calculations above to display a person's age in the text format of "yy Years, xx Months, zz Days":

          Full Age
          FullAge (calculation, text result) =
          GetAsText ( Year ( Get ( CurrentDate ) )  - Year ( Birthdate ) - If ( Get ( CurrentDate ) <  Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( Birthdate ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( Birthdate ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( Birthdate )  + If ( Day ( Get ( CurrentDate ) )  ≥ Day ( Birthdate ); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( Birthdate ) ; Day ( Birthdate ) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days "

           

          David Anders
          The Computer Guy, Seattle
          http://localwebpromotion.wordpress.com

          • 2. Re: Calculate interval in y,m,d from set record date to current date
            ArthurJoyce

            David,

            I appreciate the help, I truly do. However, I plugged your solution into the calculation field and got the same result as I was getting with the calculation I poted in my first post. I then took a very close look at your solution and found that it was identical to the calculation I was using, with the exception of the field name. So, I must  have gotten the calculation from the same place that you referenced. 

            But, I'm still back where I began. Everything started going backwards from 0 on January 1, 2012, which in reality would make ever other calculation in past years wrong as well. 

            For example, when I look at the result of that calc field for 12/25/11 it says that the time from the "current date", which happens to be 1/15/12 today, is 0 years, 0 months, 7 days. The result on 1/1/12 is 0 years, 0 months, 0 days (from 1/15/12 current date). I would think that it would say, 0 years, 0 months, 14 days and 12/12/11 should have been 0 years, o months, 21 days.

            Could it possibly be somthing related to the fact that I'm trying to do this calculation on the current year and that is somehow messing things up. But the further I move from 1/1/12 the more the results in 1990 records is going to be wrong!

            Art

            P.S. I'm editing this post to say that the result in the field for 1/8/12, with a current date of 1/15/12, is shown as -1 years, 11 months, 30 days. And the result for the record of 1/15/12 is -1 years, 11 months, 23 days.