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

# Age Calculation help needed.....

### 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.

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.....

Hi,

Are you holding this data all on one record?

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

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

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

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.....

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

Regards

Taffy