1 2 Previous Next 15 Replies Latest reply on Nov 7, 2016 9:30 AM by TSGal

    Calculation that works out age today based on date of birth?

    g4guitar

      Is their an accurate way of calculating this in Filemaker Pro. Currently I have a DOB field and the calculation ((Today - DOB)/365)-. This gets it within a few months but I want it to be accurate to the day.

       

      Thank you.

        • 1. Re: Calculation that works out age today based on date of birth?
          lexan

          Do you need something like this...?

           

          Let (

          [

             dob = ( 11 ; 4 ; 2015 )  ;                                      //  parameter or a DOB field…

             today =  Get (CurrentDate)   ;

             %years = year ( today ) - year ( dob ) ;

             %months = month ( today ) - month ( dob )  ;

             %days = day ( today ) - day ( dob )

          ]  ;

            %years &" years, " & %months & " months and " &  %days & " days!"

          )

           

          —> Result: 0 years, 0 months and 9 days!

           

          /Stefano

          1 of 1 people found this helpful
          • 2. Re: Calculation that works out age today based on date of birth?
            okramis

            Try this:

             

            Let ( [

            _agedate = GetAsDate ( Get ( CurrentDate ) + 1 - DOB )

            ] ;

            Year ( _agedate ) - 1 & " years, " & Month ( _agedate ) - 1 & " months, " & Day ( _agedate ) - 1 & " days"

            )

             

            regards

            Otmar

            • 3. Re: Calculation that works out age today based on date of birth?
              keywords

              The following will give you age in days:

              Let (

              [

                 dob = Date ( m ; d ; y )

                ; today = Get ( CurrentDate )

                ; age = today - dob

              ] ;

              age

              )

              The resulting number can then be parsed into whatever format you want your result to be in. Keep in mind, though, that age expressed as years, months and days will not always be the same for a given span of dates (e.g. 3 months can be 89, 90, 91 or 92 days depending on which set of 3 months is used, and in which year), nor will total number of days always be the same (e.g. 5 years can be 1,826 or 1,827 depending on whether the span contains one leap year or two).

              • 4. Re: Calculation that works out age today based on date of birth?
                DavidJondreau

                This doesn't give accurate results. Primarily by returning negative values for months and days.

                 

                12/20/2014 =  1 years, -1 months and -7 days!

                • 5. Re: Calculation that works out age today based on date of birth?
                  g4guitar

                  Thank you Otmar,

                   

                  Sorry I don't know what '_agedate' is referring to. Is that supposed to be field or something else?

                   

                  David

                  • 6. Re: Calculation that works out age today based on date of birth?
                    g4guitar

                    Thank you Keywords,

                     

                    I must be missing something here. When I try your formula it says 'm' is not a field. I also don't get the part of the formula age = today - dob. The calculation as I understand is within the 'age' field so using itself in the formula wouldn't work. Sorry I am a little confused.

                    • 8. Re: Calculation that works out age today based on date of birth?
                      g4guitar

                      Thank you Stefano,

                       

                      When I use your formula it returns a result of 10 years but doesn't change when I change the DOB. Am I missing something? Here is how it looks.

                       

                      Screen Shot 2015-11-14 at 11.54.43 AM.png

                      • 9. Re: Calculation that works out age today based on date of birth?
                        keywords

                        OK, let me explain:

                        1.     In the Date() function, m, d and y stand for month, day and year. Substitute the letters for numbers—e.g. Date ( 11 ; 14 ; 2015) is today's date. Alternatively, substitute the entire expression for a date field (e.g. Date of Birth if you have that in your database).

                        2.     The calc I have posted as an example uses the Let() function, which is one you really should get to know. The syntax of this function is "Let ( {[} var1 = expression1 {; var2 = expression2…]} ; calculation )". To explain this, the first part from the first { to the second } is where you declare one or more variables The squiggly brackets indicate stuff that is optional—there must be at least one variable declared, others are optional. If there are more than one variable, each must be separated by a semicolon, and the entire set must be contained within square brackets. The second part, calculation, is the expression you want as the result of the entire function.

                        3.     In my calc, dob, today and age are all variables. Because I don't want to use these variables outside of this calculation I simply name them; if I wanted to use them outside the calc (in a script, say) I would preface each with a $ or $$. Note that once a variable is declared inside the Let() calc, it CAN be referenced by other variables that follow it, hence my use of dob and today to calculate age.

                        4.     Although age is actually the result I want, I have declared it as a variable and then referenced it as the result calculation, although I could have simply used the calc, today - dob, as the second part. This is a technique I prefer because it is easier to test  this result and other variables as I build the calc.

                        5.     I have used line breaks and tabs to format the whole calc in a way that makes it more readable. It could have been written as simply:  Let ( [ dob = Date ( m ; d ; y ) ; today = Get ( CurrentDate ) ] ; today - dob ) but I find that to be less clear.

                        • 10. Re: Calculation that works out age today based on date of birth?
                          g4guitar

                          Thank you. That worked. Woohoo!

                           

                          One more question. How do I get FM to round down numbers? With age if someone is 21.997 years old (the day before they turn 22) I want the age to show 21 not round up to 22.

                          • 11. Re: Calculation that works out age today based on date of birth?
                            keywords

                            Floor() will round down, Ceiling() will round up. You could do some further maths with Round().

                             

                            Floor ( 21.997 ) = 21

                            Ceiling ( 21.997 ) = 22

                            Round ( 21.997 ; 0 ) = 22

                            Round ( 21.997 ; 0 ) – 1 = 21

                            • 12. Re: Calculation that works out age today based on date of birth?
                              okramis

                              g4guitar schrieb:

                               

                              Thank you Otmar,

                               

                              Sorry I don't know what '_agedate' is referring to. Is that supposed to be field or something else?

                               

                              David

                               

                              keywords has explained the Let-function, so that's the same here:

                               

                              _agedate = GetAsDate ( Get ( CurrentDate ) + 1 - DOB )

                              fills the local variable _agedate with the difference of today + 1 and DOB in days, converts the number of days back to a Date-format. As GetAsDate ( 1 ) (one day) returns 01/01/0001 but should be 00/00/0001, I add +1 to the current date and then subtract 1 on the Year, Month, Day-functions.

                              If you just need the year, just return Year ( _agedate ) - 1.


                              Otmar

                              • 13. Re: Calculation that works out age today based on date of birth?
                                greatgrey

                                Of course you could just use Integer of the age, it returns the integer part of a number with no rounding.

                                Int(age)

                                it may also run slightly faster, it may not mater in this case but, why make the computer work harder than necessary?

                                or

                                Truncate( age ;0)

                                does the same thing as Int(age)

                                • 14. Re: Calculation that works out age today based on date of birth?
                                  twickleberry

                                  Would you please post a screen shot of what the final script looks like? Also, what fields need to be added? (i.e.: Date of Birth

                                  Thank you!

                                  I am so new at all this - I have no clue

                                  1 2 Previous Next