1 Reply Latest reply on Feb 23, 2015 2:11 PM by philmodjunk

    Calculating Which Date Comes First



      Calculating Which Date Comes First


      I manage a program where participants are eligible for 3 years of service from the day they arrive to my area or until they turn 22 years old (which ever comes first).  What calculation can I use to determine the participant's Expiration Date (the date they are no longer eligible for services)?

      I have a the following fields in my layout:

      Today's Date (calculated), DOB (Date of Birth), QAD (Qualifying Arrival Date)  and Expiration Date.

      Again, they are eligible for 3 yrs from the QAD or until their 22nd birthday, whichever comes first.

      I am self taught out of necessity,

      Please HELP?!

      Thank You!

        • 1. Re: Calculating Which Date Comes First

          Here's a calculation for computing a person's age in years given the current date and their date of birth:

          Let (  [ B = YourTable::BirthDateField ;
                      T = Get ( CurrentDate )  
                   ] ;
                      Year ( T ) - Year ( B ) - ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )

          Substitute your birthdate field in place of "YourTable::BirthDateField" and make sure that this is a field of type date. Also make sure to select unstored as the storage option for this calculation field or the age will not automatically update as time passes.

          If you use the same calculation but substitute the QAD you can calculate the number of years elapsed since that date and then you can set up a calculation that marks a record as "expired" if the Age from QAD is 3 or more or the Age from DOB is 22 or more.