+ Start a Discussion
ArjunsArjuns 

Formula to calculate the Age in Years,Months,Days

Hi ,
Can anybody help me with the fromula field which will calulate the Age  from date of birth and display it in Years,Months & Days.?
All validations should be there.Including Leap Year Scenario.

 

BR,
Me

Navatar_DbSupNavatar_DbSup

Hi,

 

Try the below formula and made changes accordingly:

 

For example,

 

today is 11-05-2010 and mydate is 26-08-1980

To calculate the Years you could use:

                (TODAY()-Birthdate)/365.2425

 Example:

 ((11-05-2010)-(26-08-1980)/365.2425) = 29 years

 And about the months, you could use a “mod” that returns a remainder after a number is divided by a specified divisor:

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

 Example:

MOD(((11-05-2010)-(26-08-1980)), 365.2425) = 257.9675 days

257.9675/30 = 8.59891667 months

FLOOR(8.59891667)= 9 months

So you have 29 years 9 months

 

or 

try another formula

 

IF(

NOT(ISNULL(Date_of_Birth__c)), /* Condition */

TEXT(FLOOR((TODAY()-Date_of_Birth__c)/365.2425)) & " year(s) " &

TEXT(FLOOR(MOD((TODAY()-Date_of_Birth__c),365.2425)/30)) & " month(s)" & Text(Floor(MOD(Floor(MOD((TODAY()-Date_of_Birth__c),365.2425)),30))) & " day(s)" , /* Value IF True */

"" /* Value IF False */

)

 

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 

 

champvimalchampvimal

Great Answer and Explanation Ankit.

 

 

Thanks,

 

Vimal

Mark RootMark Root

I wasn't entirely happy with the common "MOD" approach to date diff and so I went old school with the formula below.  What I was finding was that, when doing a simple date comparison, the MOD formula was giving me sporadic results.  For example, comparing 1/21/2013 to 8/21/2013 produced "0 years, 7 months, and 2 days."  In looking at the two dates, though, this is clearly not right.  Where did the "2 days" come from?

 

So, ignoring the precise number of days between the two dates (212) and converting this to years, months, and days, I went with a different, albeit more complicated, approach.  Hope it helps.

 

Mark.

 

~~~~~~~~~~~~~~~~~~~~

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), & " &

TEXT(IF(DAY(Today())>=DAY(Birthdate__c),DAY(Today())-DAY(Birthdate__c),31-(DAY(Birthdate__c)-DAY(Today())))) & " day(s)"

)

Carolyn PriceCarolyn Price
Great answer, Mark. Love how accurate this formula is.

Any suggestions for including the end date in the above calculation? In your formula, the two dates 2016/01/01 and 2016/12/31 would produce "0 year(s), 11 month(s), & 30 day(s)" rather than "1 year(s), 0 month(s), & 0 day(s)."

Thanks!