 ShowAll Questionssorted byDate Posted 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 Best Answer chosen by Admin (Salesforce Developers)  AdrianCC
```  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, AdrianCC
```  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, Mark Taylor bsanh lio