You need to sign in to do that
Don't have an account?
Arjuns
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
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.
Great Answer and Explanation Ankit.
Thanks,
Vimal
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)"
)
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!
OneVanilla (https://www.onevanilla.one/)
Hello Arjuns
Please mark it as the best answer so that other people can take reference from it.Please find the solution.
Thank You
Try this formula
IF( NOT( ISBLANK( Birthdate ) ) ,
IF( DATE( 2000 , MONTH( Birthdate ) , DAY( Birthdate ) ) <= DATE( 2000 , MONTH( TODAY() ) , DAY( TODAY() ) ),
YEAR (Today()) - YEAR ( Birthdate ),
YEAR (Today()) - YEAR ( Birthdate ) -1 ),
null)
For More informattion to Refer This link
https://www.marksgroup.net/blog/salesforce-com-adding-a-formula-field-to-calculate-age-from-another-date-field/
Please mark it as the best answer so that other people can take reference from it.
Thanks!
Bhavitha.G
public static Decimal getUserAge(Date originalDate, Date currentDate){
if(originalDate != null && currentDate != null){
Decimal exactAgeInDecimal = 0.00;
//Extracting the Year , Month and Days from DOB.
Integer dobYear = OriginalDate.Year();
Integer dobMonth = OriginalDate.Month();
Integer dobDay = OriginalDate.day();
///Extracting the Year , Month and Days from Current Date.
Integer CurrentYear = CurrentDate.Year();
Integer CurrentMonth = CurrentDate.Month();
Integer CurrentDay = CurrentDate.day();
//Variable for Storing the Final Age.
Map<String,Decimal> age = new Map<String,Decimal>();
String ageString;
//Get years.
Integer yearAge = CurrentYear - dobYear;
Integer monthAge;
Integer dayAge;
//Get Months.
if(CurrentMonth >= dobMonth){
//Get months when current month is greater.
monthAge = CurrentMonth - dobMonth;
}
else{
yearAge--;
monthAge = 12 + CurrentMonth - dobMonth;
}
//get days
if(CurrentDay >= dobDay){
//get days when the current date is greater
dayAge = CurrentDay - dobDay;
}
else{
monthAge--;
dayAge = 31 + CurrentDay - dobDay;
if(monthAge < 0){
monthAge = 11;
yearAge = yearAge - 1;
}
}
//If you want to include the leapDays in Age then Please Uncomment next two lines.
//Integer leapDaysCount = yearAge / 4;
//dayAge += leapDaysCount;
//group the age in a single variable
age.put('years' , Decimal.valueOf(yearAge));
age.put('months' ,Decimal.valueOf(monthAge));
age.put('days' ,Decimal.valueOf(dayAge));
//If Years , Months and Days not equal to Zero.
if( (age.get('years') > 0) && (age.get('months') > 0) && (age.get('days') > 0) ){
exactAgeInDecimal = age.get('years') + (age.get('months') / 12) + (age.get('days') / 365);
}
//If Years and Days not equal to Zero and Months is equal to Zero.
else if( (age.get('years') > 0) && (age.get('months') == 0) && (age.get('days') > 0) ){
exactAgeInDecimal = age.get('years') + (age.get('days') / 365);
}
//If Years and Months not equal to Zero and Days is equal to Zero.
else if( (age.get('years') > 0) && (age.get('months') > 0) && (age.get('days') == 0) ){
exactAgeInDecimal =age.get('years')+ age.get('months') / 12;
}
//If Months and Days both equals to zero.
else if( (age.get('years') > 0) && (age.get('months') == 0) && (age.get('days') == 0) ){
exactAgeInDecimal =age.get('years');
}
//If Years , Months , Days is equals to Zero.
else{
return null;
}
return exactAgeInDecimal;
}
else{
return null;
}
}
IF(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))<=TODAY(),YEAR(TODAY())-YEAR(Birthdate),YEAR(TODAY())-YEAR(Birthdate)-1)
Months
IF(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))<=TODAY(),((YEAR(TODAY())-YEAR(Birthdate))*12)-MONTH(Birthdate)+MONTH(TODAY()),((YEAR(TODAY())-YEAR(Birthdate))*12)-MONTH(Birthdate)+MONTH(TODAY())-1)
Days is just
Today() - Birthdate
isn't it? I believe it accounts for leap years.
example, if birthdate is 1 Feb 1980 and today's date is 1 Feb 2023 the above formulas gives:
Years 43
Months 516
Days 15706 (which is actually 43.03013699 years if not accounting for leap years, i.e. 15706 / 365, which is 11 days extra for each leap year.. which is about right.)
changing the date by one day, if birthdate is 2 Feb 1980 and today's date is 1 Feb 2023 the above formulas gives:
Years 42
Months 515
Days 15705
You might want to add some null/blank checks to the formulas.
Please try the below provided formula field:
IF( ISBLANK(Date_of_Birth__c), NULL, IF( YEAR(TODAY()) - YEAR(Date_of_Birth__c) > 0, IF( MONTH(TODAY()) >= MONTH(Date_of_Birth__c) && DAY(TODAY()) >= DAY(Date_of_Birth__c), TEXT(YEAR(TODAY()) - YEAR(Date_of_Birth__c)) & " years, " & TEXT(MONTH(TODAY()) - MONTH(Date_of_Birth__c)) & " months, " & TEXT(DAY(TODAY()) - DAY(Date_of_Birth__c)) & " days", TEXT(YEAR(TODAY()) - YEAR(Date_of_Birth__c) - 1) & " years, " & TEXT(MONTH(TODAY()) - MONTH(Date_of_Birth__c) + 12) & " months, " & TEXT(DAY(TODAY()) - DAY(Date_of_Birth__c)) & " days" ), "Invalid Date of Birth" ) )
Hope this helps.
Thanks!