Mark Taylor

need help with code to calculate school year from dob

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

```  IF( DATE(YEAR(TODAY()), MONTH(DOB__c), DAY(DOB__c)) <= DATE(YEAR(TODAY()), 8, 31),
YEAR(TODAY()) - (YEAR(DOB__c) + 5),
YEAR(TODAY()) - (YEAR(DOB__c) + 6)) ```

This is my try as a sfdc formula field, where DOB__c is your date of birth. Another formula with a CASE for checking the integer values for consistency would also serve well I guess.

Donno what that A14 is there in your excel formula. Is it DOB?

Your formula doesn't seem 100% accurate, at least from the leap year standpoint. That .25 helps the accuracy somewhat but it's not perfect

Ty,

```  IF( DATE(YEAR(TODAY()), MONTH(DOB__c), DAY(DOB__c)) <= DATE(YEAR(TODAY()), 8, 31),
YEAR(TODAY()) - (YEAR(DOB__c) + 5),
YEAR(TODAY()) - (YEAR(DOB__c) + 6)) ```

This is my try as a sfdc formula field, where DOB__c is your date of birth. Another formula with a CASE for checking the integer values for consistency would also serve well I guess.

Donno what that A14 is there in your excel formula. Is it DOB?

Your formula doesn't seem 100% accurate, at least from the leap year standpoint. That .25 helps the accuracy somewhat but it's not perfect

Ty,

This was selected as the best answer
Mark Taylor

Thank you very much Adrian - it appears to work;)

I will let you know if I spot any problems, otherwise thanks again

bsanh lio
In a good world, this thing impacts you to be recorded as a printed version of a Rogerian piece that will be vital to you and for the opposite side. I can have https://assignmentmasters.org/ for them. Pre-sitting to create your first essay, need careful attention and explain what you rely on to write in this book.