Can any one help me with a formula that does the following:

If pupils are born on or before 31/8/97 then they are group "A"

If pupils are born between 1/9/97 and 31/8/99 group "B"

If pupils are born between 1/9/99 and 31/12/00 group "C"

If pupils are born on or after 1/1/01 group "D"

 

Where date of birth is DoB and group is Age Grade

Many thanks

The calculation is very easy to do using the Case() function  - but are you sure you want to hard-code this? Will not the pupils in group D become group C at some point?

 

Age Grade =

Case (
DoB < Date ( 9 ; 1 ; 1997 ) ; "A" ;
DoB < Date ( 9 ; 1 ; 1999 ) ; "B" ;
DoB < Date ( 1 ; 1 ; 2001 ) ; "C" ;
"D"
)

Many thanks, works like a dream.  The dates of each age grade will change each year and therefore I just need to reconfigure the calculation for that years database.

Perhaps you should consider making it a text field and populate it by script. The script could calculate the age group considering the DOB and the current year (or even the year as reflected in the file's name, if that's your naming convention).

Many thanks, works like a dream.  The dates of each age grade will change each year and therefore I just need to reconfigure the calculation for that years database.

 

Forgive my ignorance, but calculating the grade that each student is in seems like something that you'd want to calculate on the fly without have to modify a hard-coded calculation each year.  It also looks like you MIGHT be trying to evaluate your calc based upon the age of the student by using the birth dates.  Again, I'm assuming this, but I wrote a Function that will allow you to  input the date of birth of each student and then calculate which grade he should be part of.  You can change the ages of each class to match whatever your criteria is.

Let ( [
dob = <studentDOB> ;
age = Let ( [
                  d1Float = Year ( Get ( CurrentDate ) ) + Month ( Get ( CurrentDate ) ) / 100 + Day ( Get ( CurrentDate ) ) / 10000;
                  d2Float = Year ( dob ) + Month ( dob ) / 100 + Day ( dob ) / 10000
                  ];
                  Case ( 
                            IsEmpty ( dob ) ; "" ; 
                            Int ( d1Float - d2Float )
                          ))
];
Case (
     age ≤ 12 ; "Too young!" ;
     age ≤ 14 ; "A" ; //13, 14 years old
     age ≤ 16 ; "B" ; //15,16 years old
     age ≤ 18 ; "C" ; //17, 18 years old
     age ≥ 19 ; "Too old!" ;
) )

Now you can control both the Age Groups of students and check to see which age group they belong to.

It is not uncommon to establish age brackets based on age on a given date. For example, children who have reached the age of 6 on or before [ … ] will start school this year. Younger children will have to wait until next year, rather than join school mid-year immediately following their 6th birthday. In these situations the actual age of the subject, as calculated against Get (CurrentfDate), has no relevance whatsoever.