3 Replies Latest reply on Mar 14, 2017 1:51 PM by philmodjunk

    Age calculation as of a certain date?

    sdurante

      I found a script that gives me age as of CURRENT DATE:

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

       

      The result looks like this: "16 Years, 2 Months, 41 Days"

       

      What I need is age as of a certain date. I found it's not as simple as changing "Get ( CurrentDate )" everywhere it appears to a date field containing the target date.

        • 1. Re: Age calculation as of a certain date?
          Jaymo

          Just substitute the date field you want in the Let function where you see Get(CurrentDate) or specify a static date using the Date function. Make sure the calculation is set to not store calculation results, calculate when needed or it will only update when the @Date value is modified (e.g. someone changes the date field contents):

           

          --- START ---

           

          Let(

           

          [@Date = Get(CurrentDate);

          @BDay = DOB];

           

          Year(@Date) -

          Year(@BDay) - Case(@Date <

          Date(Month(@BDay); Day(@BDay); Year(@Date)); 1; 0) & " Years, "

           

          &

           

          Mod(Month(@Date) -

          Month(@BDay) + 12 - Case(Day(@Date) <

          Day(@BDay); 1; 0); 12) & " Months, "

           

          &

           

          Day(@Date) -

          Day(@BDay) + Case(Day(@Date) >=

          Day(@BDay); 0; Day(@Date -

          Day(@Date)) <

          Day(@BDay); Day(DOB); Day(@Date -

          Day(@Date))) & " Days"

           

          )

           

          --- END ---

          • 2. Re: Age calculation as of a certain date?
            sdurante

            Worked like a charm.

            THANKS!

            • 3. Re: Age calculation as of a certain date?
              philmodjunk

              Please note that this will not update as time passes if defined as an auto-enter calculation or as a stored calculation field. It would need to be defined in an unstored calculation.