Calculating School Year

  • Thread starter Thread starter Gardener
  • Start date Start date
G

Gardener

I'm trying to determine the school year that a child is
in from their date of birth. I can see that it could be
done by using a series of nested if statements, i.e. iff
birth date between x/y/zz and a/b/cc, then year 1, etc.
Can anyone think of a cleaner and easier way of doing
this?
 
Gard

Yes, there is a cleaner way. Basically you want to use some string and date functions to directly convert any date to a school year. Let's say the birthday is 9/3/1995 and the field name that contains the birthday is dtmBirthDa
format([dtmBirthday],"yyyy") or year([dtmBirthday]) to get to 1995. Then you can subtract that todays year year(Date()) to get the difference in years: 8. A think the formula to get to grade is years old minus 5. So the whole thing will be
Grade:year(dtmBirthDay) - year(date() - 5

I know the grade cut down in not conveniently January 1st and it something like November 1st. To solve that you must first add the appriapriate number of days to the date to get to January 1st. let's say the Cut off is November 11th. It takes 51 days or so to get to January 1st. So now your formula needs to be
Grade:year(dtmBirthDay+51) - year(date() - 5
Good luc

note: Date() yields the current dat

----- Gardener wrote: ----

I'm trying to determine the school year that a child is
in from their date of birth. I can see that it could be
done by using a series of nested if statements, i.e. iff
birth date between x/y/zz and a/b/cc, then year 1, etc
Can anyone think of a cleaner and easier way of doing
this
 
Tom

Thanks for that. I'll have to use a different base date,
as I'm in the UK and the base date will be sometime in
August.
-----Original Message-----
Gard,

Yes, there is a cleaner way. Basically you want to
Click to expand...
use some string and date functions to directly convert
any date to a school year. Let's say the birthday is
9/3/1995 and the field name that contains the birthday is
dtmBirthDay
format([dtmBirthday],"yyyy") or year([dtmBirthday]) to
Click to expand...
get to 1995. Then you can subtract that todays year year
(Date()) to get the difference in years: 8. A think the
formula to get to grade is years old minus 5. So the
whole thing will be
Grade:year(dtmBirthDay) - year(date() - 5

I know the grade cut down in not conveniently January
Click to expand...
1st and it something like November 1st. To solve that
you must first add the appriapriate number of days to the
date to get to January 1st. let's say the Cut off is
November 11th. It takes 51 days or so to get to January
1st. So now your formula needs to be:
 
Back
Top