Some or the other time, we would want to calculate the Age in Excel, and more often people get frustrated as they show up a little different results than expected with A day more or less and either it shows the months completed or the current running month.
Today I will show few popular Excel Formulas in practice which will calculate the Age between given Start Date and End Date in Excel and learn how accurate they are. And also we will learn those Super Hero Formulas that gives us the accurate results!
Below formulas considers the Start Date is available at Cell A1 and End Date in Cell B1 of the worksheet.
Start Date = 14-01-2013
End Date = 14-01-2015
With Excel Formula We expect the Result Age = 2 Years
Let’s see what the below formulas got to offer!!
Download Sample Excel File at the bottom of this article with Multiple Scenarios & formulas to help you understand more.
Formula 1: Easiest Among All (Date Format as [YY “Years” MM “Months” DD “Days”]) See this to learn how to apply Date Formats to Numbers
Formula: =B1-A1 Result: 01 Years 12 Months 30 Days
Accurate : NO
Formula 2:
Formula: =TEXT(B1-A1,"YYYY")-1900&" Years "&MONTH(B1-A1)-1&" Months "&DAY(B1-A1)&" Days" Result: 1 Years 11 Months 30 Days
Accurate : NO
Formula 3:
Formula: =+DATEDIF(A1,B1,"Y")&" Years, "&DATEDIF(A1,B1,"ym")&" Months, "&DATEDIF(A1,B1,"md")&" Days." Result: 2 Years, 0 Months, 0 Days.
Accurate : YES
Formula 4: Longest but Neat & Clean Formatted Results
Formula: =YEAR(DATE(YEAR(B1)-YEAR(A1),MONTH(B1)-MONTH(A1)+1,DAY(B1)-DAY(A1)+1))-1900&" Years "&IF(MONTH(DATE(YEAR(B1)-YEAR(A1),MONTH(B1)-MONTH(A1)+1,DAY(B1)-DAY(A1)+1))-1=0,"",MONTH(DATE(YEAR(B1)-YEAR(A1),MONTH(B1)-MONTH(A1)+1,DAY(B1)-DAY(A1)+1))-1&" Months ")&IF(DAY(DATE(YEAR(B1)-YEAR(A1),MONTH(B1)-MONTH(A1)+1,DAY(B1)-DAY(A1)+1))-1=0,"",DAY(DATE(YEAR(B1)-YEAR(A1),MONTH(B1)-MONTH(A1)+1,DAY(B1)-DAY(A1)+1))-1&" Days") Result: 2 Years
Accurate : YES
Formula 1 & 2 are easy, but will not give exact results as expected
Formula 3 & 4 are Super Hero Formulas which calculates Age with the accuracy of exact Years, Months and Days.
Download Sample Excel File: Calculate_Age_in_Excel_Accurately with Multiple Scenarios & formulas to help you understand more.
Also Read