5205

# 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!!

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.