 • Mark Taylor
• NEWBIE
• 0 Points
• Member since 2013

• Chatter
Feed
• 0
• 0
• 0
Likes Given
• 1
Questions
• 1
Replies

We need to calculate school year from dob.  eg if the child is born 27/08/2007 then it should calculate as year 1.  If the child is born on 01/09/2005 then it should calculate year 2.  In the UK the cut off date is 31st August so that the youngest in year 1 is born 31st August 2007, and oldest is born 01/09/2006.

The code we have for excel is

=INT((TODAY()-A14+223+(MOD(YEAR(A14),4)=0))/365.25)-5 doesn't work properly as it appears to work for jan to jan year groups

In words the formula would be:-

(todays date - dob + (next sept date-todays date))/365.25 - 5

any help on this would be appreciated

Thanks

• • May 10, 2013
• Like
• 0

We need to calculate school year from dob.  eg if the child is born 27/08/2007 then it should calculate as year 1.  If the child is born on 01/09/2005 then it should calculate year 2.  In the UK the cut off date is 31st August so that the youngest in year 1 is born 31st August 2007, and oldest is born 01/09/2006.

The code we have for excel is

=INT((TODAY()-A14+223+(MOD(YEAR(A14),4)=0))/365.25)-5 doesn't work properly as it appears to work for jan to jan year groups

In words the formula would be:-

(todays date - dob + (next sept date-todays date))/365.25 - 5

any help on this would be appreciated

Thanks

• • May 10, 2013
• Like
• 0