1 2 Previous Next 20 Replies Latest reply on Aug 2, 2016 10:56 PM by taylorsharpe

    Calculate age when born

    cillion

      Hi.

      I have a previous used database that set the age as the 2 last numbers in the year they where born.

       

      On new records now, I have the born selector return full date format like this: 29.06.1990.

       

      How could I make a second field that will calculate how old a person is with these two ways of setting the born date?

       

      Now my yearsOld field have a calculation like this:

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

       

      NB! the born field is a text field

        • 1. Re: Calculate age when born
          Johan Hedman

          FileMaker just released a documented way to calculate age

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

          2 of 2 people found this helpful
          • 2. Re: Calculate age when born
            philipHPG

            Could you please give an example of how the date of birth is stored in the old database? It is not clear to me from your explanation.

             

            If you are simply interested in the years, based on your "new" Born field, you could use a formula such as:

             

            ( Get ( CurrentDate ) - Date ( Middle ( Born ; 4 ; 2 ) ; Left ( Born ; 2 ) ; Right ( Born ; 4 ) )  ) / 365.25

             

            If you want to know the full number of years, months and days from date of birth then you can use the document linked by Johan.

            • 3. Re: Calculate age when born
              philmodjunk

              Whichever method you use, please move your birthdate data into an actual date field. That makes calculating age, sorting, finding on that birthdate much more straightforward in your solution.

              • 4. Re: Calculate age when born
                cillion

                Previus the birth year where stored like this:

                 

                And now we made a new file of the database, that we are going to import all the previus data to. So in the new file will input to the same Born field in this format:

                 

                So basicly the yearOld calculation need to calculate how old the person is by both format.

                 

                philmodjunk I know, but we have an some issues with dateformatting. So the Born field will be text intil this is fixed.

                • 5. Re: Calculate age when born
                  Johan Hedman

                  Like philmfdjunk writes, try to have date stored in data fields. There are so many advantages with that. You can always calculate the date field with dates too.

                  • 6. Re: Calculate age when born
                    cillion

                    Yes I know. But we cant import non-date number data into a date field. So it would be great if it was possible to calculate both formats intil all data is imported. And then change all the none-date data format to date format and then change the the field to date. Hope you understand

                    • 7. Re: Calculate age when born
                      Johan Hedman

                      Drop your data-file on FileMaker so that it create a new database. In there you will get fields from your import-file. Then create a calculated field that create your data into date formated data and then import that data into your solution

                      • 8. Re: Calculate age when born
                        philipHPG

                        Okay, so you can have a calculation field:

                         

                        If ( Length ( Born ) > 2 ; ( Get ( CurrentDate ) - Date ( Middle ( Born ; 4 ; 2 ) ; Left ( Born ; 2 ) ; Right ( Born ; 4 ) )  ) / 365.25 ; Year ( Get ( CurrentDate ) ) - GetAsNumber ( If ( Born < "20" ; "20" ; "19" ) & Born )  )

                         

                        Of course, this is not perfect, as it has to guess where the break is between the 1900s and 2000s. In this calculation I have set the breakpoint at 20 (anything that is 20 or higher will be interpreted as 1920+, anything less than 20 is interpreted as 2019...). You also don't have the birth month, so it is possible that the age will be off by one depending on the month of birth relative to the current month.

                        • 9. Re: Calculate age when born
                          cillion

                          Great, it worked with the 2 number format , but the 01.01.1990  format returns this :

                          I needed to use quotes on the "365.25", since it did not validate without quote?

                          • 10. Re: Calculate age when born
                            philipHPG

                            Try this:

                             

                            If ( Length ( Born ) > 2 ; Int ( GetAsNumber ( Get ( CurrentDate ) - Date ( Middle ( Born ; 4 ; 2 ) ; Left ( Born ; 2 ) ; Right ( Born ; 4 ) )  ) / 365.25 ); Year ( Get ( CurrentDate ) ) - GetAsNumber ( If ( Born < "20" ; "20" ; "19" ) & Born )  )

                             

                            I'm making it more explicit where numeric values are needed, and I've also added in the Int() function which will strip it down simply to the number of years.

                            • 11. Re: Calculate age when born
                              philipHPG

                              Ah, the requirement to put 365.25 in quotes might be a localization issue. Try 365,25

                              • 12. Re: Calculate age when born
                                cillion

                                Perfekt, it works. Thanks you very much!

                                • 13. Re: Calculate age when born
                                  jormond

                                  This calculation will be wrong on several dates each year. In a case like this, it's important to not use a calculation that we know WILL break for certain.

                                   

                                  Here is a nice discussion of age that can be helpful.

                                  Age calculation from DOB

                                   

                                  One of these calculations should be fine. I've yet to see either of them break:

                                   

                                  From comment

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

                                   

                                  From erolst:

                                  Let ( [

                                    cd =  Get ( CurrentDate )  ;

                                    dob = Birthdate

                                  ] ;

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

                                  )

                                  2 of 2 people found this helpful
                                  • 14. Re: Calculate age when born
                                    philipHPG

                                    Thank you for pointing that out, jormond. At a certain level this calculation will never be fully accurate as, in some cases, we are calculating an age simply based on a year without knowing the month. However I agree that we should avoid known problems and it is important to use best practices. I stand corrected and will add this to my repertoire.

                                     

                                    cillion please change the formula as follows (following the erolst format):

                                     

                                    Let ( [

                                         cd = Get ( CurrentDate ) ;

                                         dob = Born

                                    ] ;

                                         If ( Length ( dob ) > 2 ; Year ( cd ) - GetAsNumber ( Right ( dob ; 4 ) ) - ( Month ( cd ) < GetAsNumber ( Middle ( dob ; 4 ; 2 ) ) or Day ( cd ) <  GetAsNumber ( Left ( dob ; 2 ) ) ) ; Year ( cd ) - GetAsNumber ( If ( dob < "20" ; "20" ; "19" ) & dob ) )

                                    )

                                    1 of 1 people found this helpful
                                    1 2 Previous Next