7 Replies Latest reply on May 9, 2016 6:10 AM by RogerBirch

    Creating an "age" field

    RogerBirch

      I am trying to create a field showing the age of people in my database.

      In my "Age" field I have created a calculation of "Today's Date" (a calculation field Get (CurrentDate)) less "Date of Birth" (a text field) but this returns a figure in the number of days!!

      Could someone please point me in the correct direction to get a return in "years" please.

      Any help gratefully received, thanks

        • 1. Re: Creating an "age" field
          jormond

          Take a look at this post. It has a good explanation about calculating age.

           

          http://fmforums.com/topic/31714-age-calculation/?do=findComment&comment=143790

          1 of 1 people found this helpful
          • 2. Re: Creating an "age" field
            RogerBirch

            Many thanks Joshua, very helpful.

            • 3. Re: Creating an "age" field
              keywords

              Question: why is Date of Birth a text field? That seems odd.

              1.     A calc such as the following will give you a decimal age result—

              Let (

              [

              birth = <dateOfBirthField> ;

              now = Get ( CurrentDate ) ;

              totalDays = now - birth ;

              age = Round ( totalDays / 365 ; 2 )

              ] ;

              age

              )

              2.     You can construct a more complex calc to give a text result—

              Let (

              [

              birth = <dateOfBirthField> ;

              now = Get ( CurrentDate ) ;

              totalDays = now - birth ;

              elapsedYears =

              Year ( now ) - Year ( birth ) - If ( now < Date ( Month ( birth ) ; Day ( birth ) ; Year ( now ) ) ; 1 ; 0 ) & " years " ;

              elapsedMonths =

              Mod ( Month ( now ) - Month ( birth ) + 12 - If ( Day ( now ) < Day ( birth ) ; 1 ; 0 ) ; 12 ) & " months " ;

              elapsedDays =

              Day ( now ) - Day ( birth ) + If ( Day ( now )  ≥ Day ( birth ) ; 0 ; If ( Day ( now - Day ( now ) )  < Day ( birth ) ; Day ( birth ) ; Day ( now - Day ( now ) ) ) ) & " days" ;

              age = elapsedYears & elapsedMonths & elapsedDays

              ] ;

              age

              )

              • 4. Re: Creating an "age" field
                greatgrey

                365 makes a problem with leap years. 365.25 is more accurate. It only creates problems on nonleap years centuries. i.e. 1800, 1900, 2100. the year 2000 is a leap year.

                • 5. Re: Creating an "age" field
                  jormond

                  Using 365 and 365.25 causes many more problems than just non-leap years.

                   

                  But it comes down to this: if you KNOW your calc will return the incorrect result...why not use one that doesn't?!

                  • 6. Re: Creating an "age" field
                    jormond

                    Why not use the 2nd calc as the basis for either a straight number result, or a text result? #1 will fail in a regular and consistent pattern. #2 calculates age, a little closer to the same way we do.

                     

                    This is really a good calc that won't break:

                    Age = Year ( Get ( CurrentDate ) ) - Year ( Birthdate ) - ( Get ( CurrentDate ) < Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Get ( CurrentDate ) ) ) )

                     

                    keywords wrote:

                     

                    1. A calc such as the following will give you a decimal age result—

                    Let (

                    [

                    birth = <dateOfBirthField> ;

                    now = Get ( CurrentDate ) ;

                    totalDays = now - birth ;

                    age = Round ( totalDays / 365 ; 2 )

                    ] ;

                    age

                    )

                    2. You can construct a more complex calc to give a text result—

                    Let (

                    [

                    birth = <dateOfBirthField> ;

                    now = Get ( CurrentDate ) ;

                    totalDays = now - birth ;

                    elapsedYears =

                    Year ( now ) - Year ( birth ) - If ( now < Date ( Month ( birth ) ; Day ( birth ) ; Year ( now ) ) ; 1 ; 0 ) & " years " ;

                    elapsedMonths =

                    Mod ( Month ( now ) - Month ( birth ) + 12 - If ( Day ( now ) < Day ( birth ) ; 1 ; 0 ) ; 12 ) & " months " ;

                    elapsedDays =

                    Day ( now ) - Day ( birth ) + If ( Day ( now ) ≥ Day ( birth ) ; 0 ; If ( Day ( now - Day ( now ) ) < Day ( birth ) ; Day ( birth ) ; Day ( now - Day ( now ) ) ) ) & " days" ;

                    age = elapsedYears & elapsedMonths & elapsedDays

                    ] ;

                    age

                    )

                    • 7. Re: Creating an "age" field
                      RogerBirch

                      I found this on one of your links and it works well for me (so far!) - Year ( Get ( CurrentDate ) ) - Year (Date of Birth) - If ( Get ( CurrentDate ) < Date ( Month (Date of Birth ) ; Day ( Date of Birth ) ; Year ( Get ( CurrentDate ) ) ); 1 ; 0 )

                      Thanks to who ever posted it.