+ Start a Discussion
WilmerWilmer 

Formula to calculate Age in years and months

Hi, I need help in developing a custom formula field that calculates the Age from a given date, in terms of years and months, like this:

 

MyDate: 18/05/1990  (dd/mm/yyyy)

 

Calculated Age: 19 year(s), 11 month(s)

 

I have tried several formulas, but I only could get the year and I got problems with the months calculation.

 

Could someone help with this?

 

Regards,

 

Wilmer

Best Answer chosen by Admin (Salesforce Developers) 
MayeUPAEPMayeUPAEP

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

All Answers

SteveMo__cSteveMo__c

I think I might be close to something.  Is this for a persons age?  Does the "age" value have a maximum possible, or does it need to be open-ended? 

WilmerWilmer

Hi steve,

 

First of all, Thanks for your reply.

 

Well, I need to show the "age" of a company from its foundation date, so I think this could be open-ended as you said. There is no maximun or limited time range.

 

Regards,

 

Wilmer

MayeUPAEPMayeUPAEP

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

This was selected as the best answer
WilmerWilmer

Hi Mayela, Thanks for your help in this case, that was just what I needed it.

 

Here, it is the final developed formula:

 

 

IF(
	NOT(ISNULL(BirthDate)), /* Condition */
	TEXT(FLOOR((TODAY()-BirthDate)/365.2425)) & " year(s) " & 
	TEXT(FLOOR(MOD((TODAY()-BirthDate),365.2425)/30)) & " month(s)", /* Value IF True */
	"" /* Value IF False */
)

 The result of this formula is according to the following example:

BirthDate =10/04/1990

Today's Date = 13/05/2010

Formula Field result: 20 year(s) 1 month(s)

 

I also want to thank Steve who helped me in this case too.

 

Regards,

 

Wilmer

 

Rajesh ShahRajesh Shah

Hi,

Does this formula also take into consideration the leap years?

OnkiOnki

This formula is not working if return date is exact years e.g Start Date is 1st Oct 2010 and End date is 30th sep 2012 its show "1years 12 month" it should be "2 years 0 month". Any Idea how to solve this.

SIVASASNKARSIVASASNKAR

Dear Sir, please let me know first is it considered  for leap year also? besides that i want to calculate the exactly remaining days also . please help me