How to Calculate Age in Excel Accurately?

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

Calculate_Age_01a


 

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

Calculate_Age_02a


 

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

Calculate_Age_03a


 

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

Calculate_Age_04a


 

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

2

No Responses

Show all responses

Write a response