1 2 Previous Next 27 Replies Latest reply on Oct 7, 2016 10:36 AM by jormond

    Age calculation from DOB

    Gerrly

      I have two fields, AgeYrs and AgeMos, that calculate the patient's current age based on another field, DOB.  It works sometimes, other times it doesn't work for the years, and other times it doesn't work for the months.  Please see screenshots.  What am I doing wrong?

      Screen Shot 2015-09-17 at 11.52.13 AM.png

      Screen Shot 2015-09-17 at 11.52.25 AM.png

      Screen Shot 2015-09-17 at 11.52.32 AM.png

      Screen Shot 2015-09-17 at 11.52.45 AM.png

        • 1. Re: Age calculation from DOB
          jormond

          Is the field large enough to display the number? Are you sure that is the formula you want to use to calculate the years? There are some dates that approach may fail.

          • 2. Re: Age calculation from DOB
            antidote

            Gerrly

             

            I cannot take credit for this.  I always use this custom function to get the result you are looking for.  You want to use format 3.

            What is important is theDate.  This allows you the flexibility to sent the date being the current date or based on the Service date.

            I generally set it to the service date, because we want to know how old they were at the time of service.  And a Demographic layout I use the current date. 

             

            Great Custom Function to add to your favorites.

             

            Tim Brown

            Antidote Solutions

             

             

            ------- cut ---

            /* This is a Custom Function version of calculations provided in "Advanced FileMaker Pro 5.5" by Chris Moyer and Bob Bowers. This formula differs slightly from the ones in the book which do not account for leap years.

            Age(Birth; theDate; format)

            */


            Let ( leapFactor = If ( Mod ( Year ( theDate ) ; 4 )  = 0 ; 1 ; 0 );

            Case (

             

            // Age in years

            Format = 1 ; Year ( theDate ) - Year ( Birth ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( Birth ) );

             

            // Age in years and days

            Format = 2 ; Year ( theDate ) - Year ( Birth ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( Birth ) ) & " years and " & Case (

            ( DayOfYear ( theDate ) - leapFactor ) ≥ DayOfYear ( Birth ) ; DayOfYear ( theDate ) - leapFactor - DayOfYear ( Birth )  ;

            DayOfYear ( theDate ) + ( DayOfYear ( Date ( 12 ; 31 ; Year ( theDate ) ) - DayOfYear ( Birth ) - leapFactor ) ) ) & " days" ;

             

            // Age in years, months and days

            Format = 3 ;  Year ( theDate ) - Year ( Birth ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( Birth ) ) & " years and " & Mod ( Month ( theDate ) - Month ( Birth ) + 12 - (Day ( theDate ) < Day ( Birth ) ) ; 12 ) & " months, and " & (theDate - Date ( Month ( theDate ) - (Day ( theDate ) < Day ( Birth ) ) ; Day ( Birth ) ; Year ( theDate ) ) ) & " days"

            )

            )

            • 3. Re: Age calculation from DOB
              antidote

              Also if that is a field with the ?, make sure it is big enough to show two digits.

               

              Tim

              • 4. Re: Age calculation from DOB
                jormond

                This calc seems much more readable. And I haven't seen it break yet. ( I picked this up from Michael Horak, aka comment, a long time ago ). It can of course be extended to return the remaining months also.

                 

                Age =

                 

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

                • 5. Re: Age calculation from DOB
                  siplus

                  To start with, age is not an exact value, when you express it in years and months and you use 365.25 anywhere in your calculation. (Think about calculating the age of DOB = march 1, 2000 when current date is november 1, 2003 and you see what I mean).

                   

                  How accurate do you need to be ?

                   

                  IMHO, I am X or X-1 years old depending on if I already celebrated my birthday this year or not. That's easy to calc.

                  • 6. Re: Age calculation from DOB
                    jormond

                    Note that calc will be incorrect if you input the following dates:

                     

                    birth = 2/2/2013

                    theDate = 2/2/2016

                     

                    Result = 2 years

                     

                    We clearly 'know' that is wrong.

                    • 7. Re: Age calculation from DOB
                      jormond

                      Exactly siplus. That is what the calc I posted does.

                      siplus wrote:

                       

                      To start with, age is not an exact value, when you express it in years and months and you use 365.25 anywhere in your calculation. (Think about calculating the age of DOB = march 1, 2000 when current date is november 1, 2003 and you see what I mean).

                       

                      How accurate do you need to be ?

                       

                      IMHO, I am X or X-1 years old depending on if I already celebrated my birthday this year or not. That's easy to calc.

                      • 8. Re: Age calculation from DOB
                        erolst

                        Joshua Ormond wrote:

                         

                        This calc seems much more readable. And I haven't seen it break yet. ( I picked this up from Michael Horak, aka comment, a long time ago ). It can of course be extended to return the remaining months also.

                         

                        Age =

                         

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

                         

                        Another way is

                         

                        Let ( [

                          cd =  Get ( CurrentDate )  ;

                          dob = Birthdate

                        ] ;

                          Year ( cd ) - year ( dob ) - ( Month ( cd ) < Month ( dob ) or Day ( cd ) < Day ( dob ) )

                        )

                        • 9. Re: Age calculation from DOB
                          siplus

                          Completely agree, Joshua, but the goal of this place is also to set the poster's neurons into dance state, making them think.

                           

                          That's my post's primary ambition.

                           

                          Besides, the OP mentions patient's current age and as a developer of medical software I know that usually years are enough for looking up specific tables.

                           

                          (Unless we're in Paediatric territory, that is)

                          • 10. Re: Age calculation from DOB
                            jormond

                            Absolutely. That was my odd way of thanking you for explaining what the calc needed to do!!

                            • 11. Re: Age calculation from DOB
                              Gerrly

                              It is pediatric

                              I have no more neurons after creating this database.

                              • 12. Re: Age calculation from DOB
                                Gerrly

                                Yeah apparently the field wasn't big enough to hold double digits.  Man, do I feel like an idiot...

                                Thanks for the other calculations.  I'm changing the years one to the calculation Joshua posted.  But, what about for the months?  This is necessary as this is a peds population.

                                • 13. Re: Age calculation from DOB
                                  jormond

                                  This gives it to you in one calc that is very readable. You can split it into fields if you need to. There may be a more efficient way to calculate the 2 together, but I haven't handled months very much, but it can be calculated basically the same way. I used erolst's calculation, because it does basically the same thing but is a little more readable in this format.

                                   

                                  Let (

                                   

                                   

                                    [

                                   

                                    cd =  Get ( CurrentDate )  ;

                                    dob = Birthdate ;

                                    years = Year ( cd ) - Year ( dob ) - ( Month ( cd ) < Month ( dob ) or Day ( cd ) < Day ( dob ) ) ;

                                    months = Case ( Month ( cd ) ≤ Month ( dob ) ; 12 + Month ( cd ) - Month ( dob ) ; Month ( cd ) - Month ( dob ) ) - ( Day ( cd ) < Day ( dob ) )

                                   

                                    ] ;

                                   

                                   

                                  years & " Years " & months & Case ( months = 1 ; " Month" ;  " Months" )

                                   

                                   

                                  )

                                   

                                  Edited: Missed the day part of the calc.

                                  • 14. Re: Age calculation from DOB
                                    jormond

                                    Also, I remembered that FMI had posted a KB article about age. It uses almost the same approach, though they use the IF (). The thought process behind how they get the age is the same.

                                     

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

                                    1 2 Previous Next