In this article we’ll learn how to calculate age in excel with date of birth, calculate age in months and build a days alive calculator too.

Do note that there is no separate excel formula for age calculation. We’ll have to use a combination of different date functions.
Let’s see how!

Calculate age in excel from date of birth

When someone asks- What’s your age? We just subtract our birth year from the current year to find out our age. To calculate age in excel we have to do the same i.e. subtract birth year from current year.

Age=Current Year – Birth Year

We’ll use YEARFRAC excel formula for age calculation. Here’s the syntax:
YEARFRAC(Start Date, End Date, Basis)

1. Type =YEARFRAC(A2, TODAY(), 1) Our start date is the date of birth, end is date is the current date and basis is 1 which tells Excel to divide the actual number of days per month by actual number of days per year
   
   
   
2. Drag the formula down and here you go. We’re able to calculate age in excel from date of birth
   
   
   
3. To round the value use =ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)
   
   
   

How to calculate age in excel using Today Function

1. Type =(TODAY()-A2)/365 in the cell and hit enter.
   
   
   
2. Drag the formula down to other cells. Now you can see the age in excel.
   
   
   
3. To round up the value you can use =INT((TODAY()-A2)/365)
   

Note: This method doesn’t take into account leap years i.e. 366 days so in some cases the values might be 100% accurate.

How to calculate age in excel using DATEDIF Function

Another formula we can use to build an age calculator from date of birth is DATEDIF. We can use this formula to not only calculate age in years but also in number of months and number of days.

Syntax:
=DATEDIF(Start Date, End Date, Unit)

Unit Values:
Y – return number of years
M – return number of months
D – return number of days
YM – return number of months, ignoring days and years
MD – return number of days, ignoring months and years
YD – return number of years, ignoring days and months

Let’s see different variations of the DATEDIF formula to calculate age in excel.

To calculate age in years, use =DATEDIF(A2,TODAY(),”Y”)

To calculate age in months, use =DATEDIF(A2,TODAY(),“M”)

To calculate age in years, months and days all together, use
=DATEDIF(A2,TODAY(),”Y”)&” Years “&DATEDIF(A2,TODAY(),”YM”)&” Months “&DATEDIF(A2,TODAY(),”MD”)&” Days “

This is how you can build age calculator from date of birth in excel.

Calculate age on specific date in excel

To calculate age as of a certain date we can use DATEDIF formula. But instead of using TODAY we’ll have to input a specific date.

1. Type =DATEDIF(A2, B2, “Y”) and hit enter.
   
   Now you can see age as of specific dates.
   
   
2. To get the age in years, months and days all together, use
   =DATEDIF(A2, B2, “Y”) & “Years,” & DATEDIF(A2, B2, “YM”) & “Months,” & DATEDIF(A2, B2, “MD”) & “Days”
   

You can also use DATEDIF to calculate age as per a specific year. In case you don’t have a full date but year, you can use the same the calculate age.

Type =DATEDIF(A2, DATE(B2,1,1),”Y”) and hit enter

Calculate age when one attains N years of age

Let’s say you want to find out the date when you’ll turn 60 years of age. You can use the DATE formula for the same.

Type =DATE(YEAR(A2)+$E$1, MONTH(A2), DAY(A2)) and press enter.

The formula will find out the date when you’ll be 60 years old. You can change the number in D2 to find date according to different years of age.

Conclusion

This was all about how to calculate age in excel from date of birth.

To recap there is no excel formula for age calculation, you have to use existing DATE functions to calculate age in excel from date of birth.

If you have any questions or tips, I’d love to hear them in the comment section below.