Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mark TaylorMark Taylor 

need help with code to calculate school year from dob

Please could you help us

 

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) 
AdrianCCAdrianCC
  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,

 Adrian

All Answers

AdrianCCAdrianCC
  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,

 Adrian

This was selected as the best answer
Mark TaylorMark Taylor

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

 

I will let you know if I spot any problems, otherwise thanks again
bsanh liobsanh 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.