10 Replies Latest reply on Mar 15, 2017 11:05 AM by philmodjunk

    calculating age issues


      I have a calculation in place to determine the age of my students, but it's not calculating it correctly.  I am using Age = Year(Get(CurrentDate)-_Date of Birth) -1  Then I changed it out to get rid of the Year and the -1   My info didn't change.  


      I have a student who has a DOB of 12/22/2005 and yet, it is showing that his age is 10.11.  He was 11 in December, so this should be showing him as just over 11, not almost 11.  Everyone is just a little less than what he or she should be.


      Can anyone help?

        • 1. Re: calculating age issues

          You have two issues: calculating the correct age and making sure the field updates with the passage of time.

          The following calculation cannot be used in an auto-entered calculation nor a stored calculation. It must be an unstored calculation:


          Let ( T = get ( CurrentDate ) ;

                  Year ( T ) - Year ( DOBfield ) -

                    ( T < date ( Month ( DOBfield ) ; Day ( DOBfield ) ; Year ( T ) ) )

                 ) // let

          • 2. Re: calculating age issues

            Date of Birth in your case here is a date, you are subtracting a date from a number, which will not work.


            Age is such a common calculation that filemaker actually has a whitepaper on it:

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


            From that page, Age is calculated:


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


            Notice that they are using the Year() function around the Birthdate field as well, which you were not. They are also using an If() calculation on if the additional year should be selected for the current year.


            So in your case, just substitute your fields into the above:

            Year ( Get ( CurrentDate ) ) - Year ( _Date of Birth ) - If ( Get ( CurrentDate ) < Date ( Month ( _Date of Birth ) ; Day ( _Date of Birth ) ; Year ( Get ( CurrentDate ) ) ); 1 ; 0 )


            Also, I tend to avoid leading underscores in field names, they can cause trouble when you least expect it. Same with numbers, special characters and long strings of words.

            • 3. Re: calculating age issues

              Thanks.  I'm not happy with the underscores leading the field names myself, but I imported all the fields from another database that didn't play nice when I was updating machines and I had no choice and I had to use Filemaker to rebuild my entire database.  It was too much trouble....along with doing my every day job....to try to rename all the fields.  It's been months of trying to find free time to get some of the fields to be filled in correctly and work somewhat correctly.  This age one is more annoying than anything, but when a kid needs to be evaluated, I'd like the age to be correct. 


              I'll see what I can do with your suggestion.  It's been a long time since I had to actually think about calculations and how they all worked together.  Thanks again.

              • 4. Re: calculating age issues

                I update my current date every day, this is a database I use every day and I do notice changes, but not nearly what they should be.  Thanks for your response.  It's been so long since I had to run any scripts or calculations that I'm really rusty.  I borrowed the one I was using, so I'm amazed that it gave me anything remotely correct. 

                • 5. Re: calculating age issues

                  "updating your current date every day"


                  will have no no effect on how these calculations update.

                  • 6. Re: calculating age issues

                    The reason why the calculation is not correct is,

                    date - date

                    is number of days, if you apply it in year function

                    Year ( date - date )

                    the function need parameter as date, so the "number of days" is treated as the day after "number" days from "yesterday of 1/1/1" (1 is treated as 1/1/1, 366 is 1/1/2)

                    each year have different days with leap year calendar system, so this is not make sense.


                    But the calculation returns always integer number, so if you get 10.11, you did some other things. (not using unstored calculation field but auto-enter calculation ?)

                    • 7. Re: calculating age issues

                      Here is a custom function that might help: Age ( birthday; today; format )



                      The function will calculate a persons age. The result of this function can be displayed in four different formats:


                      1: Years

                      2: Years and Days

                      3: Years, Months and Days.

                      4: Years ¶ Months ¶ Days

                      • 8. Re: calculating age issues

                        I won't accept that, even with changing it to use my field names. 

                        • 9. Re: calculating age issues

                          I need to have an age in years and months.  We're talking students, here and I need to know how old with the month for when they will be tested and just how close they are to turning a certain age.  I was close to what I needed with the calculation I got here from another discussion, but it's just off by a few months.


                          So far, none of the suggestions have changed what I'm getting for an age for the student I mentioned above.  I expected I would have gotten something different, but so far, nothing.  It's always 10 years, 11 months.

                          • 10. Re: calculating age issues

                            I expected I would have gotten something different, but so far, nothing.


                            I have twice pointed out that all of these calculations can have update issues.


                            How are you implementing this calculation?


                            1. Setting a field's value in a script?
                            2. A stored Calculation field?
                            3. An Unstored Calculation field?
                            4. A number or text field with an auto-enter calculation?


                            1. will only update each time you run the script

                            2 and 4 will not update unless a field referenced in the calculation is modified

                            4 might not update even if a field referenced in the expression is modified if you don't clear the "do not replace..." check box.

                            3 will update every time you display the field on a layout, but being unstored brings its own limitations to what you can do with the field.


                            The suggested custom function is close enough to what you want that very little additional work would be needed to get the Year.Month result that you've requested.