You need to sign in to do that
Don't have an account?
Wilmer
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
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
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?
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
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
Hi Mayela, Thanks for your help in this case, that was just what I needed it.
Here, it is the final developed formula:
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
Hi,
Does this formula also take into consideration the leap years?
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.
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
The following code determine the difference between 2 dates incorporating leap years and months having 30/31 days.
Thanks.