How can I calculate current school year from DOB ongoing?

[Bek]

From a list of dates of birth I need to show what school year they are in
today, but have it so it is always correct in the future too.

I can caculate their current school year using a vlookup table of dates of
birth between Sept and August and the relevent school year, but come
September 2009 this will be wrong.

i.e. if born 30th January 1996 today you will be in school year 8.
If I check the spreadsheet in January 2010 I need it to be telling me you
are now in year 9.

School year runs from 1st September to 31st August.

Any help much appreciated!

 

[Pete_UK]

With a date of birth in A1, try this in B1:

=INT((TODAY()-A14+223+(MOD(YEAR(A14),4)=0))/365.25)-5

I've checked this for 1992 to 2003, and it seems to correctly
distinguish different years at 1st September.

Hope this helps.

Pete

 

[Sheeloo]

Suppose you have DOB in A1
For current year, enter 8/31/2008 in B1
Enter this in C1
==DATEDIF(A1,B1,"y")

This will give you age in years as on 8/31/2008

Now you can check this in D1 like this
=IF(C1=5,"KG",C1-4)
assuming you do not have kids who are not in school

otherwise
=IF(C1>4,IF(C1=5,"KG",c1-4),"Too young to be in school)

You can get a formula to give you 8/31/2008 if you do not want to enter a
date once a year

 

[Pete_UK]

Sorry, I tested this on row 14 and forgot to change the references
when posting. It should read:

=INT((TODAY()-A1+223+(MOD(YEAR(A1),4)=0))/365.25)-5

if you put it in B1.

Hope this helps.

Pete

 

[Ron Rosenfeld]

From a list of dates of birth I need to show what school year they are in
today, but have it so it is always correct in the future too.

I can caculate their current school year using a vlookup table of dates of
birth between Sept and August and the relevent school year, but come
September 2009 this will be wrong.

i.e. if born 30th January 1996 today you will be in school year 8.
If I check the spreadsheet in January 2010 I need it to be telling me you
are now in year 9.

School year runs from 1st September to 31st August.

Any help much appreciated!

How old does someone have to be to enter school? What is the cutoff date?
--ron

 

[Bob I]

How old does someone have to be to enter school? What is the cutoff date?
--ron

Not to mention the exceptional/special student not following that yearly
schedule.

 

[Bek]

Ahh, well in the UK this rarely happens and for the group of people I am
analysing this shouldn't be an issue

 

[Bek]

Hello

School years run from 1 - 14 with Reception and Nursery prior to that,
however, the people I'm using will be from approx year 8 onwards.

N - 4th birthday between 1st Sept and 31st Aug
R - 5th birthday between 1st Sept and 31st Aug
1 - 6th birthday between 1st Sept and 31st Aug
2 - 7th birthday between 1st Sept and 31st Aug
3 - 8th birthday between 1st Sept and 31st Aug
4 - 9th birthday between 1st Sept and 31st Aug
5 - 10th birthday between 1st Sept and 31st Aug
6 - 11th birthday between 1st Sept and 31st Aug
7 - 12th birthday between 1st Sept and 31st Aug
8 - 13th birthday between 1st Sept and 31st Aug
9 - 14th birthday between 1st Sept and 31st Aug
10 - 15th birthday between 1st Sept and 31st Aug
11 - 16th birthday between 1st Sept and 31st Aug
12 - 17th birthday between 1st Sept and 31st Aug
13 - 18th birthday between 1st Sept and 31st Aug
14 - 19th birthday between 1st Sept and 31st Aug

Year 14 is the last.

cheers

 

[Bek]

no worries

it works perfectly for this year - does the 'today' bit ensure it will be
correct next year? Also - what does the '223' bit mean??

Sorry to ask, I am self taught!

 

[Ron Rosenfeld]

Hello

School years run from 1 - 14 with Reception and Nursery prior to that,
however, the people I'm using will be from approx year 8 onwards.

N - 4th birthday between 1st Sept and 31st Aug
R - 5th birthday between 1st Sept and 31st Aug
1 - 6th birthday between 1st Sept and 31st Aug
2 - 7th birthday between 1st Sept and 31st Aug
3 - 8th birthday between 1st Sept and 31st Aug
4 - 9th birthday between 1st Sept and 31st Aug
5 - 10th birthday between 1st Sept and 31st Aug
6 - 11th birthday between 1st Sept and 31st Aug
7 - 12th birthday between 1st Sept and 31st Aug
8 - 13th birthday between 1st Sept and 31st Aug
9 - 14th birthday between 1st Sept and 31st Aug
10 - 15th birthday between 1st Sept and 31st Aug
11 - 16th birthday between 1st Sept and 31st Aug
12 - 17th birthday between 1st Sept and 31st Aug
13 - 18th birthday between 1st Sept and 31st Aug
14 - 19th birthday between 1st Sept and 31st Aug

Year 14 is the last.

cheers

Still not clear, but:

Putting this together with your example of the child born 30 Jan 1996 being in
the 8th year, I conclude that is the case since, by 9/1/2009, he will be 13
(he's only 12 now).

That being the case, try this formula:

=DATEDIF(A1,DATE(YEAR(A2)+(MONTH(A2)>8),8,31),"y")-5

A1 contains the Date of Birth
A2 contains the date against which you are testing (e.g. TODAY(), or some fixed
date).

Since you said the range of students would be in years 8-14, I did not bother
to test for years outside of that range (e.g. for N, R, or too old) but that
could be done with some nested IF statements.
--ron

 

[Ron Rosenfeld]

Sorry, I tested this on row 14 and forgot to change the references
when posting. It should read:

=INT((TODAY()-A1+223+(MOD(YEAR(A1),4)=0))/365.25)-5

if you put it in B1.

Hope this helps.

Pete

With your formula, someone born 1 Sep 93 would be in the 11th year; 2 Sep 93
would be in the tenth year. I think that is not correct based on the chart the
OP provided with the cutoff date being 1 Sep.
--ron

 

[Pete_UK]

Yes, it was correct yesterday, but it has moved on by one day and will
continue to do so in its current form.

I'll have another look at it tomorrow.

Pete

 

[Pete_UK]

Okay, with dates of birth starting in A1, put this in B1:

=DATEDIF(A1,DATE(YEAR(TODAY()),8,31),"y")-5

and copy down.

Tested for various dates of birth spanning 1995 to 2003, and values of
TODAY going out to 2013.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Okay, with dates of birth starting in A1, put this in B1:

=DATEDIF(A1,DATE(YEAR(TODAY()),8,31),"y")-5

and copy down.

Tested for various dates of birth spanning 1995 to 2003, and values of
TODAY going out to 2013.

Hope this helps.

Pete

Not quite Pete.

The problem with this approach is exemplified by the following, which includes
some assumptions which might be US-centric.

The school year likely begins 9/1

Given the OP's student DOB of 1/30/96,

If Today = today (22 Jan 09) then he is in the 8th year. However, come 1 Sep
2009, he should be in his ninth year. Your formula will still return 8 up
through 31 Dec 2009.

I think my previously posted suggestion:

=DATEDIF(A1,DATE(YEAR(A2)+(MONTH(A2)>8),8,31),"y")-5

where

A1: DOB
A2: date to be tested, could be TODAY()

will return the desired answer (if my various assumptions are correct).

--ron

 

[Pete_UK]

Hi Ron,

yes, you're right - in my testing I was more concerned with ensuring
the change of year group always happened on 1st September and didn't
notice that the year groups hadn't changed with values of today beyond
1st Sept in any one year.

I've tested your formula with my set-up and yours works.

Drat !! <bg>

Pete