14 Replies Latest reply on Mar 25, 2015 9:23 PM by biggorilla

    Calculating Age

    gvklimetz

      I'm not very experienced with calculations and need a little help with a calculation that the result is a persons age when you have monthbirth / daybirth / yearbirth in seperate fields, this gives you a value in a dob_ct field. (showing total dob as 04/10/1954 instead of seperate fields). The calc then shows age as a number 58 and needs to also break down the age to month day year when the person is less than 2 years old.

       

      I know some of you wizz's out there can handle this.......

      In advance, thanks.

       

      Greg

        • 1. Re: Calculating Age
          stephensexton

          Hi Greg -

           

          Here are two calculations that might help here... the second one for "AgeText" shows a field called "DOB" which you could swap for your "dob_ct" field.  Variables could also be used if necessary to neaten up the second calculation - e.g. use a "Let" calculation to first set a $dob variable and $currentdate variable.  I'm sure there could be a better way to do the second one also.  AgeDecimal is handy as it allows for easy sorting and searching of age ranges etc. 

           

          AgeDecimal (calculation type is number) = Round ( (Get (CurrentDate ) - dob_ct ) /365.25 ; 2 )

           

          AgeText =

          GetAsText ( Year ( Get ( CurrentDate ) )  - Year ( DOB ) - If ( Get ( CurrentDate ) <  Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Yrs, " &

          GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( DOB ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( DOB ) ; 1 ; 0 ) ; 12 ) ) & " Mths, " &

          GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( DOB )  + If ( Day ( Get ( CurrentDate ) )  ≥ Day ( DOB ); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( DOB ) ; Day ( DOB ) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days"

          • 2. Re: Calculating Age
            Mike_Mitchell

            Greg -

             

            A simplified calculation will give you the age in years:

             

            Let ( [ todaysDate = Get ( CurrentDate ) ; DOB = Date ( monthField ; dayField ; yearField ) ] ;

             

             

            Year ( todaysDate ) -

            Year ( DOB ) -

            (

            todaysDate < Date (

            Month ( DOB ) ;

            Day ( DOB ) ;

            Year ( todaysDate )

            )

            )

            )

             

            I'm not sure what you mean by "break down the age to month day year". Can you elaborate? Do you want a number of years, a number of months, and a number of days? Or do you want to call out that the child is "X months" old, as parents often do?

             

            Mike

            • 3. Re: Calculating Age
              gvklimetz

              Hey Mike, thanks again…….. as far as under 2, looking for year/months/days…… this is for a medical application and they asked for that break down,

               

              Gregory V. Klimetz

              gvklimetz@mac.com

              • 4. Re: Calculating Age
                Mike_Mitchell

                Still not sure what that means. If the child is, say, exactly 18 months old, is that 1 year, 6 months, 0 days? Or are you looking for 1 year, 18 months, and 547.5 days?

                • 5. Re: Calculating Age
                  gvklimetz

                  Sorry, yes I'm looking for 1 year, 6 months, 0 days

                   

                   

                  Gregory V. Klimetz

                  gvklimetz@mac.com

                  • 6. Re: Calculating Age
                    Order_from_Chaos

                    I had a similar need and modified a calculation first put together by Winfried Huslik so it would behave similarly to the Excel function DateDif.  You can find it here:

                     

                    http://www.briandunning.com/cf/1326

                     

                    It does require FileMaker Pro Advanced as it is setup as a custom function. Whenever you need the difference between two dates displayed broken down by years, months and days, just set the flag to "T" and the result will be returned in that format.

                     

                    HTH,

                     

                    Brett

                    • 7. Re: Calculating Age
                      Mike_Mitchell

                      Thanks, Brett. I was working this up in the shower this morning. Spared me the trouble.  

                       

                      Mike

                      • 8. Re: Calculating Age
                        DavidJondreau

                        Keep in mind calculating "months" requires some arbitrary decision making on your part.

                         

                        You will inevitabilty end up with ages of the same number of days but different month and day results. Basically, any solution will be off by a couple days at times.

                        • 9. Re: Calculating Age
                          Order_from_Chaos

                          Agreed. There are several threads on this discussion you can check out. In the end, Greg, you'll have to decide if this covers your needs as no solution to this problem is perfect.

                           

                          Brett

                          • 10. Re: Calculating Age
                            gvklimetz

                            Thanks everyone that has sent me some help………

                             

                            Gregory V. Klimetz

                            gvklimetz@mac.com

                            • 11. Re: Calculating Age
                              biggorilla

                              Hi Mike - thanks for this calculation! I just plugged into my solution and it worked great!  I understand the Let statement but I'm not sure how the 2nd part of the calculation works?

                              Year ( todaysDate ) -

                              Year ( DOB ) -

                               

                              // why do we need " < " in the calculation - does it only evaluate when today's date is less than the Date (DOB)?

                              (

                              todaysDate < Date (

                              Month ( DOB ) ;

                              Day ( DOB ) ;

                              Year ( todaysDate )

                              )

                              )

                              )

                               

                              Also - I need to calculate how old someone will be on 9/1 of the current year.  Would I substitute 9/1/Year Get (CurrentDate) for "todaysDate"?

                               

                              Thanks Again!

                              • 12. Re: Calculating Age
                                Mike_Mitchell

                                The calculation does this (in English):

                                 

                                Subtract the DOB year from the current year. Then subtract 1 if you haven't yet reached your birthday this year.

                                 

                                The "todaysDate < DOB" part is a Boolean expression that will evaluate to 1 if it's true (i.e., if the current date is earlier than the birthdate). That way, you will get an accurate reading.

                                 

                                For example, this is 2015. I was born in 1966. If you subtract those, you get 49. But since I haven't reached my birthday yet this year, I'm still 48.

                                 

                                To answer your other question, yes, you can substitute another date for todaysDate. Just use:

                                 

                                Date ( month ; day ; year )

                                 

                                In your example, that would be:

                                 

                                Date ( 9 ; 1 ; Year ( Get ( CurrentDate )))

                                 

                                HTH

                                 

                                MIke

                                • 13. Re: Calculating Age

                                  Making your script or function multi-phasic is useful

                                   

                                  Let's name the caluclation/custom function GetAge , you can name it as you please.

                                   

                                  Then a simple parameter can be passed:

                                   

                                  If age < 2

                                     getage("Months")

                                  else if (xxx)

                                    getage("Years and Months")

                                  else

                                    getage("Years")

                                  end if

                                   

                                  Disregarding leap years and birthdates of Feb 29 which really make things difficult for such calculations, let's let FileMaker do the work not to mention those that reverse day, month, year for their own or system settings...

                                   

                                  Convert your three fields into a FileMaker Date Field

                                   

                                  dateofbirth = date(datefieldmonth;datefieldday; datefieldyear )

                                  birthdatethisyear = date(datefieldmonth;datefieldday; year (get(currentdate) )

                                   

                                  Now you have two considerations for age: is the birthdate greater or less than today.

                                  birthdate =

                                   

                                  if ( birthdatethisyear < get(currentdate)

                                    Years = year(dateofbirth) - year(get(curentdate)

                                  else

                                     Years = year(dateofbirth) - year(get(curentdate) + 1

                                  end if

                                   

                                  (You might check which one of the above gets the +1)

                                   

                                  Months are a bit trickier since they loop 1 to 12 but the idea is the same.

                                   

                                  Stardates are much easier.  Why it is important to know that a child is two months rather than three months is interesting. Obviously some big change occurs on the first day of each month? Or some statistician might not have a job otherwise? 

                                  • 14. Re: Calculating Age
                                    biggorilla

                                    Thank you Mike!  Great explanation!  I didn't know you could do a Boolean expression in a Calc without an If or Case statement - (maybe that's why I'm a newbie).  I was born in 1961 so I'm older but not wiser!

                                     

                                    Have a great week!

                                     

                                    Alberto