4 Replies Latest reply on Oct 25, 2010 8:51 AM by taffy

    Age Calculation help needed.....

    taffy

      Title

      Age Calculation help needed.....

      Post

      I have a FM database of youth soccer players - including their birth dates.

      Based on birth dates I need FM to calculate what age group the player is eligible to play in.

       

      For instance - if the player is born on or between 8/1/92 and 7/31/93 - then they can play on a U18 team.

       

      Can somebody help me with the Filemaker formula / process that will return the age group eligibility based on birth date.

      Thanks in Advance....

       

       

      U18 8/1/92 – 7/31/93 

      U17 8/1/93 – 7/31/94 

      U16 8/1/94 – 7/31/95 

      U15 8/1/95 – 7/31/96 

      U14 8/1/96 – 7/31/97 

      U13 8/1/97 – 7/31/98 

      U12 8/1/98 – 7/31/99 

      U11 8/1/99 – 7/31/00 

      U10 8/1/00 – 7/31/01 

      U9 8/1/01 – 7/31/02 

      U8 8/1/02 – 7/31/03 

      U7 8/1/03 – 7/31/04 

      U6 8/1/04 – 7/31/05 

      U5 8/1/05 – 7/31/06 

      U4 8/1/06 – 7/31/07

        • 1. Re: Age Calculation help needed.....
          revmk

          Hi,

          Are you holding this data all on one record?

          • 2. Re: Age Calculation help needed.....
            taffy

            yes.... its a calculated field based on the contents of the birthdate that is entered

            • 3. Re: Age Calculation help needed.....
              philmodjunk

              Basic approach is to use a Case Function, but since these date ranges change each year, the actual dates should be computed as a function of the current date so you don't have to manually update the values every year.

              First the bare bones case function:

              Case ( BirthdateField >  Date ( 7 ; 31 ; 1993 ) and BirthdateField < Date ( 8 ; 1 ; 92 ) ; "U18" ;
                          BirthdateField > Date ( 7 ; 31 ; 1994 ) ; "U17" ;
                          BirthdateField > Date ( 7 ; 31 ; 1995 ) ; "U16" ;

                         //continue this pattern


                          BirthdateField > Date ( 7 ; 31 ; 2007 ) ; "U4" ;
                          "Ineligible" )


               Now to improve it with a calculation to adjust the dates, year by year:

              Let ( Y = Year ( Get ( CurrentDate ) - 3 ) ;
                       Case ( BirthdateField >  Date ( 7 ; 31 ; Y - 15 ) and BirthdateField < Date ( 8 ; 1 ; Y - 14 ) ; "U18" ;
                                  BirthdateField > Date ( 7 ; 31 ; Y - 13 ) ; "U17" ;
                                  //And so on
                                  BirthdateField > Date ( 7 ; 31 ; Y ) ; "U4" ;
                                  "Ineligible" 
                         ) // Case
                       ) // Let

              This calculation for year may need to be changed to accomodate the current month as well as year depending on what you need for your data base
                         

              • 4. Re: Age Calculation help needed.....
                taffy

                Phil - thank you very much for the insights and "complete" solution - much appreciated.

                Regards

                Taffy