+ Start a Discussion
Ravi Dutt SharmaRavi Dutt Sharma 

Calculate the age in years and months using Formula field

I have DOB (API Name : BirthDate__c) and I want to calculate age in years and months. I have created two formula fields
 
Age in years

FLOOR((TODAY()- BirthDate__c )/365.2425)
 
Age in months (remaining months)

FLOOR(MOD((TODAY()-BirthDate__c),365.2425)/30)

If date of birth is 11 Aug 2013 and todays date is 11 Aug 2015, this gives result as 1 year 12 months. Instead the result should be 2 years 0 months.
Best Answer chosen by Ravi Dutt Sharma
William TranWilliam Tran
Ravi,

Here's how I would approach it:

FORMULA for YEAR:
IF (  (MONTH(Today()) +12 - MONTH(BirthDate__c ))>=12, YEAR(Today())-YEAR(BirthDate__c), YEAR(Today())-YEAR(BirthDate__c) -1)

FORMULA for MONTH:
IF (  (MONTH(Today()) +12 - MONTH(BirthDate__c ))>=12,  (MONTH(Today()) +12 - MONTH(BirthDate__c ))-12, (MONTH(Today()) +12 - MONTH(BirthDate__c )))

Thx

All Answers

Prabhat Kumar12Prabhat Kumar12
Use following formula

IF(Today()<BirthDate__c , "Check the Date!",

TEXT(IF(YEAR(Today())=YEAR(BirthDate__c ),0,IF(YEAR(Today())-YEAR(BirthDate__c )=1,IF(MONTH(Today())=MONTH(BirthDate__c ),IF(DAY(Today())<DAY(BirthDate__c ),0,1),IF(MONTH(Today())<MONTH(BirthDate__c ),IF(DAY(Today())<DAY(BirthDate__c ),1,0),1)),IF(MONTH(Today())-MONTH(BirthDate__c )<0,YEAR(Today())-YEAR(BirthDate__c )-1,IF(MONTH(Today())=MONTH(BirthDate__c ),IF(DAY(Today())<DAY(BirthDate__c ),YEAR(Today())-YEAR(BirthDate__c )-1,YEAR(Today())-YEAR(BirthDate__c )),YEAR(Today())-YEAR(BirthDate__c )))))) & " year(s), " &

TEXT(IF(YEAR(Today())=YEAR(BirthDate__c ),IF(MONTH(Today())=MONTH(BirthDate__c ),0,IF(MONTH(Today())>MONTH(BirthDate__c ),IF(DAY(Today())<DAY(BirthDate__c ),MONTH(Today())-MONTH(BirthDate__c )-1,MONTH(Today())-MONTH(BirthDate__c )),0)),IF(MONTH(Today())=MONTH(BirthDate__c ),IF(DAY(Today())<DAY(BirthDate__c ),11,0),IF(MONTH(Today())>MONTH(BirthDate__c ),IF(DAY(Today())<DAY(BirthDate__c ),MONTH(Today())-MONTH(BirthDate__c )-1,MONTH(Today())-MONTH(BirthDate__c )),IF(MONTH(Today())<MONTH(BirthDate__c ),IF(DAY(Today())<DAY(BirthDate__c ),11-(MONTH(BirthDate__c )-MONTH(Today())),12-(MONTH(BirthDate__c )-MONTH(Today()))),12-(MONTH(BirthDate__c )-MONTH(Today()))))))) & " month(s)"

)

 
Ravi Dutt SharmaRavi Dutt Sharma
Hi Prabhat,

Thanks for the reply. Instead of so many checks, can we not use below?
 
Age in years

FLOOR((TODAY() + 1 - BirthDate__c )/365.2425)
 
Age in months (remaining months)

FLOOR(MOD((TODAY() + 1 - BirthDate__c),365.2425)/30)



 
William TranWilliam Tran
Ravi,

Here's how I would approach it:

FORMULA for YEAR:
IF (  (MONTH(Today()) +12 - MONTH(BirthDate__c ))>=12, YEAR(Today())-YEAR(BirthDate__c), YEAR(Today())-YEAR(BirthDate__c) -1)

FORMULA for MONTH:
IF (  (MONTH(Today()) +12 - MONTH(BirthDate__c ))>=12,  (MONTH(Today()) +12 - MONTH(BirthDate__c ))-12, (MONTH(Today()) +12 - MONTH(BirthDate__c )))

Thx
This was selected as the best answer
Ravi Dutt SharmaRavi Dutt Sharma
Thanks William
denisse Solisdenisse Solis
If date of birth is 11 Aug 2013 and todays date is 11 Aug 2015, this gives result as 1 year 12 months. Instead nether portal calculator (https://nethercraft.net/nether-portal-calculator.php) the result should be 2 years 0 months.
David John 47David John 47
Thank you so much for posting this. It is very informative.
You can visit Assignment Writing Experts in London if you need any help with your assignments.
https://www.globalassignmenthelp.com/assignment-help-london