You need to sign in to do that
Don't have an account?

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
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
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
Thank you very much Adrian - it appears to work;)
I will let you know if I spot any problems, otherwise thanks again